How to display a user determined by the maximum number rows

0
votes

I'm using laravel 4.2 and I have the following table that looks like this

ID|chosen(boolean('0','1'))|subject|user_id
 1|1                       |cricket|2
 2|0                       |cricket|3
 3|1                       |rugby  |2

I would like to choose the user_id that has the most chosen rows or in other words the user that has the most 1s in their chosen column. So in this case user 2 would be the correct result.

I believe this is the answer

$table = DB::table('tablename')
         ->select(array(DB::raw('count(user_id) AS CountUser')))
          ->where('chosen', 1)
         ->orderBy('CountUser', 'desc')
         ->groupBy('user_id')
         ->first();

however how do I display the user_id in my view? For example:

{{$table->user_id}} //should give me '2'

Error message reads:

Undefined property: stdClass::$user_id

1
sqllaraveleloquentblade

1 Answers

0
votes

You are selecting the count only. Try this

$table = DB::table('tablename')
     ->select(DB::raw('count(user_id) AS CountUser, user_id'))
      ->where('chosen', 1)
     ->orderBy('CountUser', 'desc')
     ->groupBy('user_id')
     ->first();