0

I want to use the following query.

SELECT `name`, COUNT(`user_id`) AS total, SUM(`status` = 'Done') as done 
    FROM posts GROUP BY `name`, `user_id`, `status`

But when I implemented this...

Post::selectRaw('count(user_id) as total')
    ->selectRaw('SUM(status = "Done") as done')
    ->groupBy('name')
    ->get();

My data table doesn't display any data. Is there something wrong in my query?

Karl Hill
  • 6,972
  • 3
  • 39
  • 64
Vysco Zyza
  • 85
  • 7
  • use the ``toSql`` method to see what query your builder generates. – Niteya Shah Feb 11 '21 at 13:47
  • 2
    There is a sum and count functions in laravel (search for aggregate function laravel) you can use them and you can use this `->select(DB::raw('SUM(status = "Done") AS done')))` head to those 2 answers which can help you [Link_1](https://stackoverflow.com/questions/30424949/laravel-query-builder-sum-method-issue) and [Link_2](https://stackoverflow.com/questions/38005224/laravel-use-count-sum-method-after-groupby). – Mohamed Bdr Feb 11 '21 at 13:50

1 Answers1

1

This should work:

$posts = DB::table('posts')->select(DB::raw('count(user_id) as total'))->selectRaw('SUM(status = "Done") as done')->groupBy('name')->get();

Since you didnt provide any info about the models, migrations or logic, im guessing that you have everything else set up correctly.

Also the links that Mohamed Bdr added are great examples and I recommend checking them out.

dz0nika
  • 41
  • 6
  • I got this error when trying your code ```undefined property:stdClass::$id``` – Vysco Zyza Feb 11 '21 at 14:13
  • Try dd($posts) or print_r($posts); and let me know what pops up – dz0nika Feb 11 '21 at 14:21
  • im using print_r($posts); and the page shown ```Illuminate\Support\Collection Object ( [items:protected] => Array ( [0] => stdClass Object ( [total] => 24 [done] => 3 ) ) )``` – Vysco Zyza Feb 11 '21 at 14:32
  • So stdClass is just a empty class that you fill when you need it, you can read more here [link](https://stackoverflow.com/questions/931407/what-is-stdclass-in-php?rq=1) . In your instance your count is 24 and the sum is 3. Are you passing any $id variable that may be interfering, in the route, or controller? – dz0nika Feb 11 '21 at 14:38
  • ah yes, im passing $row->$id for CRUD action in datatable, what should I do to fix this? – Vysco Zyza Feb 11 '21 at 14:47
  • Well the $id is returning null, $row is also probably null (Im guessing), so see why is that and there is your problem, try and fix it and best of luck. – dz0nika Feb 11 '21 at 14:50