Sortable based on average in related table

[eswachsman]

I have a column which uses an Eloquent accessor which averages a column on a related table. The column works fine but I want to be able to sort on that column. I do realize that it is not really possible to sort in the database on an accessor because accessors are only run after the query executes. So I guess I have to make the average in the query.

How can I sort a column based on the average for a related hasMany relationship?

Here is my table structure
parents

• id

variations

• id
• parent_id
• price

Here is my relationship on the Parent model

public function variations()
    {
        return $this->hasMany(Variation::class);
    }

Here is my accessor on Parent model to get the average of its' variation prices

 public function getAveragePriceAttribute()
    {
       return round($this->variations->average('price'), 2);
   }

Here is how I generate the column using the accessor
Column::make('Average Price', 'average_price')->sortable();

How can I make the sortable do the same thing as my accessor would do?

[rappasoft]

You can pass a callback to the sortable: https://github.com/rappasoft/laravel-livewire-tables/wiki/Using-the-built-in-sorting

Might be able to do it with a subquery from there.

[BenOussama180]

hello @eswachsman , i have the same case as you but i can not access my custom attribute in the component ?
`protected $appends = [
'is_on_order',
];

public function orders()
{
    return $this->hasMany(Order::class, 'link_id');
}
public function getIsOnOrderAttribute()
{
    return $this->orders->where('user_id', auth()->id())->isEmpty();
}`

BooleanColumn::make("Purchased Before", 'is_on_order'), this does not work & it throws this error :
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'links.is_on_order' in 'field list' (Connection: mysql, SQL: select count(*) as aggregate from (select links.site as site, links.price as price, links.as as as, links.traffic as traffic, links.cuntry as cuntry,

[BenOussama180]

hello @eswachsman , i have the same case as you but i can not access my custom attribute in the component ?
`protected $appends = [
'is_on_order',
];

public function orders()
{
    return $this->hasMany(Order::class, 'link_id');
}
public function getIsOnOrderAttribute()
{
    return $this->orders->where('user_id', auth()->id())->isEmpty();
}`

BooleanColumn::make("Purchased Before", 'is_on_order'), this does not work & it throws this error :
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'links.is_on_order' in 'field list' (Connection: mysql, SQL: select count(*) as aggregate from (select links.site as site, links.price as price, links.as as as, links.traffic as traffic, links.cuntry as cuntry,