Sort and Filter by a Specific Field of Relation Table

[KLA6]

Is your feature request related to a problem? Please describe.

I'm always frustrated when I make a layout with relation table and field, e.g., ...

• Let's say every post has one author, and in here, author is actually an id of an user, so I will use user_id instead of author in this post.
• When we make a table of posts, if we wants to sort() or filter(), we can easily make it with the user_id.
• But actually sort or filter the user_id is not meaningful, because users expect it with user_id.name or user_id.email.

I've already seen there are some discussions about this issue as well (e.g., https://github.com/orchidsoftware/platform/discussions/1766), but it seems using Eloquent Filter (https://orchid.software/en/docs/filters/#eloquent-filter) is the only way to solve it, however it is a little bit complicated.

---

Describe the solution you'd like

So, what I want to suggest is...

• What if the model is already defined its relationship with belongsTo() or hasOne(), meaning a clear situation which shows 1:1 relation...
• Orchid provides a simpler coding way to make its sort and filter, e.g., ...

public function columns(): array { return [

  TD::make( 'user_id.name' , 'Author Name'  )->sort()->filter( Input ::make() )
  TD::make( 'user_id.email', 'Author Email' )->sort()->filter( Input ::make() )

  # I hope just calling by dot notation makes sorting and filtering to be possible.

]; }

---

Describe alternatives you've considered

Because the Eloquent Filter gives the alternative solution, so my request is only about coding convenience.

I always really appreciate Orchid's cool structure which makes productions quick, and this suggestion may be loved by others as well. : )

---

Additional context

What if there is another easy way for this, and if I couldn't know it, I want to apologize first, and also please let me know that. T T

[Edinburgher]

@tabuna This is something a lot of people struggle with as there is also no documentation on how to achieve this with sorts. Sorts are not filters. My solution feels hacky and I don't know if you intended a sort using a relation to be implemented like this. To use the sort in a table I named the TD 'customer_name' and added the CustomerNameSort Filter to the query in the list screen.

<?php

namespace App\Orchid\Filters;

use App\Models\Customer;
use Illuminate\Database\Eloquent\Builder;
use Orchid\Filters\Filter;

class CustomerNameSort extends Filter
{
    /**
     * The array of matched parameters.
     *
     * @return array|null
     */
    public function parameters(): ?array
    {
        return ['sort'];
    }

    /**
     * Apply to a given Eloquent query builder.
     *
     * @param Builder $builder
     *
     * @return Builder
     */
    public function run(Builder $builder): Builder
    {
        $sort = $this->request->get('sort', '');
        if (str_contains($sort, 'customer_name')) {
            $direction = str_starts_with($sort, '-')? 'desc' : 'asc';
            return $builder->orderBy(Customer::select('name')
                ->whereColumn('customers.id', 'orders.customer_id')
                ->orderBy('name')
                ->limit(1), $direction);
        } else {
            return $builder;
        }
    }
}