0

I'm trying of get the product most selled of each community by the suppliers, However I'm getting always the product 1. My goal is only get one product "the most selled" according the community of the supplier.

This is my db schema:

enter image description here

And this is my current query using Laravel:

$products = Purchase::select([
    'purchases.supplier_id',
    'purchases.id as purchId',
    'suppliers.id as supliId',
    'suppliers.community_id',
    'purchase_details.product_id',
     'purchase_details.qty',
     'purchase_details.purchase_id',
  ])
  ->selectRaw('sum(purchase_details.qty) as sum')
  ->join('suppliers','purchases.supplier_id','suppliers.id')
  ->join('purchase_details','purchases.id','purchase_details.purchase_id')
  ->groupBy('suppliers.community_id')
  ->groupBy('purchase_details.product_id')
  ;
  

  $sql = $products->toSql();
  $result = \DB::table( \DB::raw("({$sql}) as sub") )
 ->mergeBindings($products->getQuery())
 ->groupBy('community_id')
 ->join('communities','sub.community_id','communities.id')
 ->leftJoin('products','sub.product_id','products.id')
 ->select('communities.name as communityName',
        'products.name as productName')

 ->selectRaw('max(sub.sum) as total_ventas')
 ->get();
return $result;

Output:

[
{
"communityName": "Chunchos",
"productName": "Product 1",
"total_ventas": "1.00"
},
{
"communityName": "Colpas",
"productName": "Product 1",
"total_ventas": "4.00"
},
{
"communityName": "Punas",
"productName": "Product 1",
"total_ventas": "8.00"
}
]

SQL Query output:

select `communities`.`name` as `communityName`,
    `products`.`name` as `productName`,
    max(sub.sum) as total_ventas 
    from (select `purchases`.`supplier_id`,
          `purchases`.`id` as `purchId`,
          `suppliers`.`id` as `supliId`,
          `suppliers`.`community_id`,
          `purchase_details`.`product_id`,
          `purchase_details`.`qty`,
          `purchase_details`.`purchase_id`,
          sum(purchase_details.qty) as sum from `purchases`
     inner join `suppliers` on `purchases`.`supplier_id` = `suppliers`.`id`
     inner join `purchase_details` 
  on `purchases`.`id` = `purchase_details`.`purchase_id`
  where `purchases`.`deleted_at` is null
  group by `suppliers`.`community_id`, `purchase_details`.`product_id`) as sub
  inner join `communities` on `sub`.`community_id` = `communities`.`id` 
  inner join `products` on `sub`.`product_id` = `products`.`id`
  group by `community_id`

Why my joins are not working?

FreddicMatters
  • 175
  • 12
  • I would suggest getting the resulting SQL from the final query and adding it to the question, **after** properly indenting and formatting it. – miken32 Dec 06 '21 at 21:01
  • Hi @miken32 I have updated my question, now I can't execute in phpmyadmin because of this message `this is incompatible with sql_mode=only_full_group_by` could you help me? – FreddicMatters Dec 07 '21 at 15:26
  • Does this answer your question? [How can I solve incompatible with sql\_mode=only\_full\_group\_by in laravel eloquent?](https://stackoverflow.com/questions/43776758/how-can-i-solve-incompatible-with-sql-mode-only-full-group-by-in-laravel-eloquen) – miken32 Dec 07 '21 at 15:46
  • You can disable strict mode, or add all your grouping columns to your select. Looks like `community_id` on the outer query is causing the problem in this case. – miken32 Dec 07 '21 at 15:47

0 Answers0