This is more of a SQL question than a Laravel one.

I'd like to accomplish something like the following based on my User model with the created_at field. (table users in database)

Any idea on how to do so ?

What I have done so far (using Eloquent ORM) :

User::query()
   ->selectRaw("COUNT(*) created_users_to_this_date, DATE_FORMAT(created_at, '%Y-%m') date")
   ->orderBy('date')
   ->groupBy('date')
   ->get();

Equivalent SQL request

select COUNT(*) created_users_to_this_date, DATE_FORMAT(created_at, '%Y-%m') date from `users` where `users`.`deleted_at` is null group by `date` order by `date` asc

Thus returning

I thank you for your help

CodePudding user response：

If your mysql version support window function, you can try to use SUM window function to do cumulative count

DB::table(DB::raw('(select COUNT(*) created_users_to_this_date, DATE_FORMAT(created_at, \'%Y-%m\') date 
    from `users` 
    where `users`.`deleted_at` is null 
    group by `date`) t1'))
->select('created_users_to_this_date','date',DB::raw('SUM(created_users_to_this_date) OVER(ORDER BY date) total_users_created_to_date'))
->get();

CodePudding user response：

your equivalent sql will be

SELECT DATE ,
  @running_number:=@running_number created_users_to_this_date AS created_users_to_this_date
   FROM (SELECT 
  COUNT(*) AS created_users_to_this_date,
  DATE_FORMAT(created_at, '%Y-%m') DATE
FROM
  users 
where users.deleted_at is null 
GROUP BY `date` 
ORDER BY `date` ASC ) final
JOIN (SELECT @running_number:=0) rn

CodePudding user response：

User::query()
 ->select('
 DB::raw('COUNT(DATE_FORMAT(created_at, \'%Y-%m\') = DATE_FORMAT(now(), \'%Y-%m\')) as created_users_to_this_date'),
 DB::raw('COUNT(DATE_FORMAT(created_at, \'%Y-%m\') <= DATE_FORMAT(now(), \'%Y-%m\')) as total_users_created_to_date'),
 DB::raw('DATE_FORMAT(created_at, '%Y-%m') as date')
 ')->orderBy('date')->groupBy('date')->get();