groupBy() eloquent query

I'm not really sure what the issue is. If the orders table has product_id 1 and is returned in the query it's already finalized right. The group by is not gonna sort it by the the number of grouped items. You need a complete different query for that. I don't think that's possible with whereHas.

Also whereHas is just checking if the items are there, it's not returning them. If you want that you need to use with instead.

Can you maybe explain what you want to achieve and what relations you have?

@bobbybouwmann

The query itself should return all products, ordered by the number of successful sales (orders). The orders itself are not required, it should just generate a "top products" view for each seller.

Should I use with, even though the orders are not required in the final view?

But you don't have anything in your query which will order the results

@snapey

How to order the product model, when using whereHas()? The order relation is only accessible in the callback, isn't it?

whereHas is used to select only parents that have children matching a condition. Ordering should not enter into it.

Your groupBy statement only has the effect of leaving out confirmed products with 0 orders.

Therefore confirmedProducts 1 and 2 should be listed. If you want these ordered by the number of orders then you need to add something else. You cannot do it within whereHas.

just as @bobbybouwmann explained

But how group and sort them in the end?

    $result = $userProfile->confirmedProducts()->with(['orders' => function ($query) {
        $query->finalized();
    }])->groupBy(XX)->sortBy(ZZ)->take(2)->get();

so I get the products in order by their successful sales?

Why don't you just write an eloquent query and use an orderby after the groupby, looks like

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

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1

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

Just example, adjust aggregate to your needs.

A good read: https://www.yiiframework.com/wiki/2541/when-to-use-active-record

There are times active record is fine, other times maybe not.

You can count the orders for each product.

$userProfile->confirmedProducts()->whereHas('orders', function ($query) {
    $query->finalized();
})->withCount('orders')->orderBy('orders_count','desc')->take(5)->get();

@snapey

sounds like a plan, is there a way without using withCount()? How to make sure, only orders which are finalized are included into the calculation? -Thanks a lot for your time.

Yes, create a new relationship called finalizedOrders().

$userProfile->confirmedProducts()->whereHas('finalizedOrders')->withCount('finalizedOrders')->orderBy('finalizedOrders_count','desc')->take(5)->get();

Your new relationship should be the same as orders() but with whatever where statement you are using in finalized()