Finding the most repeating id by laravel eloquent group

Question

$device = SalesItem::where('type', '=', 1)->get()->groupBy('product_id');

There is a list of products in the database. Here I am storing the product id. The same product can be idsi. I want to get the id data of the product with the same id at most.

Sample:

ID   type   product_id
1    1        1
2    1        1
3    1        1
4    1        1
5    1        1
6    1        2
7    1        2
8    1        1
9    1        1
0    1        1

output: 1

here I want the output to give product id 1. I couldn't find what to do after group by. Can you guide me on this?

Answer 1

I don't think there is an elegant way to do this in, but this should work for you:

Note: I'm unsure of your table name.

$device = DB::table('sales_item')
          ->select('product_id', DB::raw('COUNT(product_id) AS magnitude'))
          ->where('type', 1)
          ->groupBy('product_id)
          ->orderBy('magnitude', 'DESC')
          ->limit(1);

It goes into the table and selected the id, type, and the COUNT() of product_id. Which it then groups by the product_id and orders by the count('product_id') from high to low. ->limit(1) is used to only select the first (read highest) value.