Order model by eloquent relationship column value

Hello, am hoping someone can help me with a problem am having.

Am trying to order my items model by a specific column on eloquent relationship.

I have tried the following code:

$items = Item::where('is_active', true)->with(['products' => function($query) {
            $query->where('is_active', true);
        }])->withCount(['products' => function($query) {
            $query->select(DB::raw('min(min_price) as price'));
        }])->orderBy('products_count', 'desc')->paginate(20);

The product model has min_price column which I want to order by, at the moment am getting random results.

Any help would be appreciated.

LaryAI

3 years ago

Level 58

To order the items model by a specific column on the eloquent relationship, you can modify the orderBy clause to include the relationship column name. In this case, you can use 'products.price' to order by the min_price column on the products relationship. Here's an example:

$items = Item::where('is_active', true)
    ->with(['products' => function($query) {
        $query->where('is_active', true);
    }])
    ->withCount(['products' => function($query) {
        $query->select(DB::raw('min(min_price) as price'));
    }])
    ->orderBy('products.price', 'desc')
    ->paginate(20);

This should order the items by the minimum price of their active products.

strafe

3 years ago

Level 3

Lary's response didn't work, i get the following error now

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'products.price' in 'order clause'

PovilasKorop

3 years ago

Level 11

@strafe I don't really understand the logic of ordering by withCount:

->withCount(['products' => function($query) {
            $query->select(DB::raw('min(min_price) as price'));

This part doesn't make much sense: so you're counting all the products of an item - why select min price here?

Shouldn't that select min price be in the with() and not in the withCount()?

I would generally suggest you write an SQL query on paper first, so you would understand what is actually being queried, and then transform it into Eloquent.

Please or to participate in this conversation.