Search and sort by aggregate

[mr-Sepi0l]

Hey there,

I've been searching for hours to figure how to sort a list by an aggreate column and got stuck.
Let assume I have a CompaniesController with the following methods:

 public function sortableBy(): array
    {
        return ['actions_count'];
    }

    public function aggregates(): array
    {
        return ['actions'];
    }

actions is a relationships in my Company model.

When I dump the SQL generated it actually generate the aggregate in the query but when I add the sort actions_count, the query try to order by companies.actions_count instead of actions_count.

Would you know a workaround other than building the whole query by myself just for that detail ?
Thanks, let me know if I can provide further informations.

[v-radev]

@mr-Sepi0l hey, I just ran this in a project that I have a similar aggregate and it looks like it fails exactly because of the table prefix before the count field in the order clause. I was able to fix it by extending the Orion\Drivers\Standard\QueryBuilder@getQualifiedFieldName(string $field) and adding a condition:

if (str_ends_with($field, '_count')) {
    return $field;
}

Basically if the string ends with _count just don't add the table prefix. Let me know if you need more details about this. Also I can do a PR if the maintainer is interested in some kind of check like this one.

[mr-Sepi0l]

Looks like it could be a solution. Where did you extended the method, i've check controller and model and this method doesn't seems to be extended there ?

Thanks anyway and sorry for the long answer :)

[v-radev]

@mr-Sepi0l you can create your own query builder class and implement \Orion\Contracts\QueryBuilder. That class needs to extend \Orion\Drivers\Standard\QueryBuilder and you can just overwrite the getQualifiedFieldName() method. Finally to make Orion use this query builder you have to bind the query builder contract to you class in the service container, this can be done in you AppServiceProvider@register() class.

[v-radev]

This will of course be a problem if a you have an actual field in the table called something_count, so a more complex condition needs to be applied here, but I hope you get the idea.

[mr-Sepi0l]

I don't so far ^^ but thanks for the thought ^^