Laravel query in database

am new in laravel and I am getting it hard in building difficult queries. I have a project where I need to get which product is most sold for a week or for a month. I have three tables in database: products orders order_items

The table of order_items is attached below.enter image description here

This is the query that I have done to get mos sold product for this week. But I know that is not right. It gets me only the first product.

For example: If I have a order where id = 5 and i have 4 items, it gets me the first one. Also even if the second product has its' quantity more than the first one, it still shows the first product. Can you please help me ?


$most_sold_product_week=Order::join('orders_item','orders.id', '=','orders_item.id_order')
    ->where('orders.status_order','=','approved')
    ->whereBetween('orders.created_at', [Carbon::now()->startOfWeek(),Carbon::now()->endOfWeek()])
    ->select('id_product')
    ->groupBy('id_product')
    ->orderByRaw('COUNT(*) DESC')
    ->limit(1)
    ->value('id_product');

jlrdw

7 years ago

Level 75

Can you please properly format the code:

https://help.github.com/en/articles/creating-and-highlighting-code-blocks

1 like

Alphal

7 years ago

Level 1

@JLRDW - I did it

jlrdw

7 years ago

Level 75

Here is a join group by I used as example that works:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
          ->select('dc_powners.ownerid', 'dc_powners.oname')->distinct()
          ->selectRaw('count(dc_pets.petid) as countOfPets')
          ->groupby('dc_powners.ownerid')
          ->orderby('dc_powners.oname')
          ->get();

It gives a pet owner and count of their pets only:

owner   |   countOfPets

john     2
mary     6

Have you tried a left join. Sometimes these queries require some trial and error to work out.

As example see: https://laracasts.com/discuss/channels/eloquent/orderby-computed-related-attribute

How even with assistance, it took time to work out.

Also have you considered using eloquent relations:

https://laravel.com/docs/5.8/eloquent

https://laravel.com/docs/5.8/eloquent-relationships

Just keep in mind, eloquent queries and even complex normal queries can take some playing around with to get right.

1 like

Please or to participate in this conversation.