How to sort by field of related model?

[Estigy]

I have a list of musical instruments. Each instrument is related to a category (flutes, clarinets, trumpets,...), and the category has a sort field (flutes at the top, drums at the bottom).

I already can display the list of instruments together with their related category in a Table, but how do I make Orchid sort by the category's sort field when clicking on the header?

On the page https://orchid.software/en/docs/quickstart-sort-filter-table/ the manual says that "Note. In this way, you cannot sort related models.", which is not really helpful as it does not indicate whatsoever how the problem could be tackled.

The manual states on https://orchid.software/en/docs/filters/ that
http://example.com/demo?sort=content.ru.name
will lead to
$model->orderBy('content->ru->name', 'asc');
which seems to be exactly what I need - but how do I make the header of the category column sort this way?

Thanks in advance, Estigy

[plamenradev]

Same question :) How can we use sort with dot notation?
I write my own class, which extends Orchid\Filters\HttpFilter and overwrites addSortsToQuery method. The purpose of overwriting the method is to include joins to the builder with the related records, but probably is not the best solution.

[GR0ZA]

Hi, did you find another solution for sorting related models? I'm facing the same problem right now.

[plamenradev]

Nope, now I'm trying to order results by HasOneThrough relation.

[ValerianMemsk]

Just use an eloquent filter: https://orchid.software/en/docs/filters/#eloquent-filter
Create a class like in examlpe, and define methods like that

public $parameters = ['category'];

public function run(Builder $builder): Builder {
       return $builder->whereHas('category', function (Builder $query) {
          $query->where('category_name_field', $this->request->get('category'));
        });
}

 public function display(): iterable {
      return [
        Input::make('category')
          ->type('text')
          ->value($this->request->get('category'))
          ->placeholder('...')
          ->title('Search')
      ];
    }

And then in your screen's query() method smth like:

return [
  'instruments' => Insrument::filters([yourFilterClass::class])->simplePaginate();
]

[plamenradev]

@ValerianMemsk , we are talking about ordering the results by relation field, not filtering the results.
Imagine you have many cars with category (car brand as a relation) and you want to order the cars by brand name, for example if you have brands = BMW, Toyota, Honda, Opel.... order the cars by brand - first BMW's cars, then Honda's cars, then Opel's cars, then Toyota's cars...

[c-gross]

I have it working in my project to be able to sort and filter on relationship field but was some time ago.

Try to use the id field of that relationship. I think that was the only thing to do.

TD::make('category_id', __('Category'))

In my case the URI looks like that: ?sort=partner_id

What I also did, but that should not be required. Just for better performance, is to add the relationship to the query() method.
... ::filters()->with('partner')-> ...

[stas12go]

Hello! Still need a better solution to this problem.

[c-gross]

Maybe not the best way if the relationship has many entries.

TD::make('partner_id', __('Partner'))
    ->sort()
    ->filter(TD::FILTER_SELECT, Partner::all()->pluck('name','id'))
    ->render(function (Product $product) {
        return $product->partner->name;
    }),

[Edinburgher]

I posted a working but not optimal solution here: #2234 (comment)