1

The idea is to get the top sale products from the order table.

Order::select('product_id', 'price')->get()->groupBy('product_id')
    ->map(function ($row) {
       return $row->count('product_id');
});

The Order table looks like this

enter image description here

I get something like:

product_id: count

"8" : 2,
"34": 1,
"36": 1,
"28": 1,
"31": 1,
"40": 1,
"44": 1,
"46": 1,
"47": 2

But I still need to order by the count. How do I do it?

Expected 

"8" : 2,
"47": 2,
"34": 1,
"36": 1,
"28": 1,
"31": 1,
"40": 1,
"44": 1,
"46": 1,
owen
  • 115
  • 5
  • 12

3 Answers3

3

You can use the sortBy method on your collection:

Order::select('product_id', 'price')->get()->groupBy('product_id')
->map(function ($row) {
   return $row->count('product_id');
})->sortBy('count');

If you wish to sort descending:

Order::select('product_id', 'price')->get()->groupBy('product_id')
->map(function ($row) {
   return $row->count('product_id');
})->sortByDesc('count');

If you wish to sort using sql:

Order::select('product_id', 'price',\DB::raw('COUNT(product_id) as count'))
->groupBy('product_id', 'price')
->orderBy('count')->get();

Note: You may need to specify the columns in your select clause in your ->groupBy:

->groupBy('product_id', 'price')
Adam Rodriguez
  • 1,850
  • 1
  • 12
  • 15
  • *Note: You may need to specify the columns in your select clause in your ->groupBy:* give me a big clue to solve this problem. I end up having to remove 'price' from ::select() because I only want to group by 'product_id_count'. Thank you! – owen Nov 27 '18 at 02:15
1

You need to do something like this:

Order::select('product_id', 'price',\DB::raw("COUNT('product_id') as product_count"))->groupBy('product_id')->havingRaw("COUNT('product_id') > ?", [0])->orderBy('product_id','ASC')->get();
Marco
  • 517
  • 3
  • 10
  • Your code is sort by product_id. What I need is sort by the number of count of the product_id – owen Nov 26 '18 at 06:43
  • I updated my answer, can you check if this works for you? – Marco Nov 26 '18 at 13:24
  • Hmm. No luck. I get this error. SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database.orders.price' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select `product_id`, `price`, COUNT('product_id') as product_count from `orders` group by `product_id` order by `product_id` asc) – owen Nov 26 '18 at 13:59
  • Uhmm maybe [this](https://stackoverflow.com/questions/43776758/how-can-i-solve-incompatible-with-sql-mode-only-full-group-by-in-laravel-eloquen) can help you – Marco Nov 26 '18 at 16:00
  • 1
    @owen You'll need to match the columns in your select clause with your group by. So if your select clause contains product_id, and price the group by will need these columns specified as well. The column `product_count` does not need to by in the group by clause as it is computed. – Adam Rodriguez Nov 26 '18 at 16:10
1

Try this one,

Order::select('product_id', 'price',\DB::raw('COUNT(product_id) as count'))->groupBy('product_id')->orderBy('count')->get();

Here, raw query is used to count the product ids and then grouped so that we can get count for each product id and then finally ordered by count as per requirement.

I hope you will understand and if any explanation needed regarding above code, feel free to ask.

Sagar Gautam
  • 9,049
  • 6
  • 53
  • 84
  • I get this error. SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database.orders.price' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select `product_id`, `price`, COUNT(product_id) as count from `orders` group by `product_id` order by `count` asc) – owen Nov 26 '18 at 06:46