Convert Query Builder to Eloquent

0
votes

I have these tables:

  • products: id, name
  • orders: id, number
  • order_items: order_id, product_id

So I want to get best order products. And i make this code below.

$sales = DB::table('products')
            ->leftJoin('order_items','products.id','=','order_items.product_id')
            ->leftJoin('orders','orders.id','=','order_items.order_id')
            ->selectRaw('products.*, COALESCE(sum(orders.item_count),0) total')
            ->groupBy('products.id')
            ->orderBy('total','desc')
            ->take(6)
            ->get();

Please help me convert query above to eloquent. This work but I want to get products morphed images too.

2
laraveleloquent
Share the models for these tables in the post. Also, explain too what exactly is this query doing, and provide some example using data. - Aashish gaba
Have you created the models for these tables, if so please share them here. - Aashish gaba

2 Answers

1
votes

Assuming you created proper relationships in the Product model, would look something like this:

Product::with('order_items', 'orders')
    ->selectRaw('COALESCE(sum(orders.item_count),0) as total')
    ->orderByDesc('total')
    ->limit(6)
    ->get();
1
votes

Eloquent also support leftJoin, so you can do something like this:

$sales = Product::query()
            ->leftJoin('order_items','products.id','=','order_items.product_id')
            ->leftJoin('orders','orders.id','=','order_items.order_id')
            ->selectRaw('products.*, COALESCE(sum(orders.item_count),0) total')
            ->groupBy('products.id')
            ->orderBy('total','desc')
            ->take(6)
            ->get();

But if you mean you want to use eager load, please share your models and these relations first,