Laravel relation withAggregate

Question

I am trying to order related Models by a custom column of a nested relation

I have these Models

Product with productDefaultSpecs, which is a hasMany(ProductDefaultSpec::class) relation

ProductDefaultSpec with productSpecType, which is a belongsTo(ProductSpecType::class) relation

ProductSpecType has a order_value (int) field

I achived some expected behaviour with this relation

public function productDefaultSpecs()
{
    return $this->hasMany(ProductSpec::class)
        ->withAggregate('productSpecType', 'order_value')
        ->orderBy('product_spec_type_order_value', 'DESC');
}

When I retrieve Products from the DB, the ProductDefaultSpec are ordered as expected.

But when I try to use e.g. $product->productDefaultSpecs()->delete() I get a SQL Error

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

Where is the mistake? Or is there a better way to get custom ordered related models?

Answer 1

The issue you're encountering arises from attempting to order by a virtual column (product_spec_type_order_value) that doesn't exist in your underlying table. Laravel's withAggregate() method creates an aggregate column but does not actually create a physical column in the table.

To achieve custom ordering based on a nested relation's column, you can use a join in your query. Here's how you can modify your relation to achieve the desired result:

public function productDefaultSpecs()
{
    return $this->hasMany(ProductSpec::class)
     ->join('product_spec_types', 'product_default_specs.product_spec_type_id', 
    '=', 'product_spec_types.id')
    ->orderBy('product_spec_types.order_value', 'DESC')
    ->select('product_default_specs.*');
}

This modification joins the product_spec_types table and orders the results based on the order_value column of the product_spec_types table. We then select only the columns from the product_default_specs table to avoid any conflicts.

With this modification, your query should work as expected, and you should no longer encounter the SQL error when deleting related models.