Search and sort by fields of relation is not possible on the index-page

[v1nk0]

At this moment, the query to select the records on a index-page of a resource does not include any relations that are configured. Due to this, it's not possible to search through fields of relations or sort on fields of relations that are shown in the list.

Simple example:
I have a Post and a User resource.
Post belongs to User.
User hasMany Post.
The models and Nova resources are set-up correctly.

When on the index-page of a the posts, I can't search for a post by the name of the user, nor can a sort the list of posts by the name of the user (as a matter of fact, you can add sortable to the user-relation, but it will not work as Nova will look for a field 'user' in the posts table and not for the field 'name' within the users table, again the issue with the index-query not containing relations)

I've added the user relation for the Post resource as follows, but it does not change anything:
public static $with = ['user'];
User is of course the name of the function that I use in the Post model to configure the belongsTo user.

Of course you could 'just' go the User resource index-page, search for the user here, open the user's detail-page and look (and search) into the Posts there, but as this is just a simplified example there are real-life applications where it makes a lot of sense to be able to search on fields of relations as well. Also, this does not cover the "can't sort by relation field" problem.

I have managed to make it work by overriding the indexQuery of the Post resource and add a join with the users table here, but this doesn't feel right as I needed workarounds to avoid problems like "SQL: Not unique table/alias" as Nova DOES automatically make a join when the posts are listed (which is more-or-less also an index for posts) on the User resource detail-page. It's also really slow!

[v1nk0]

My work-around solution at this moment (which is not perfect, slow and does not cover the sorting!):

    public static function indexQuery(NovaRequest $request, $query)
    {
        $query->select('posts.*', 'u.name');
        $query->join('users as u', 'posts.user_id', '=', 'u.id');
        return $query;
    }

I had to use an alias for the users table here as there are situations where Nova itself already joins a table using users as the table name (e.g. on a detail page of a different resource listing the Post resource as hasMany or ManyToMany relation)

I also had to use select to be sure it takes all the fields from the posts table and only the name from the users table (without this, for all fields that share a name between the two tables (like id!) the value of the users table was used, which messed everything up)

Now I could use the name of a user for searching by adding u.name to the list of fields to search through:

    public static $search = [
        'id',
        'title'
        'u.name',
    ];

[danrichards]

You can work around the order as well. #156

[v1nk0]

@danrichards yes, this work-around works for the sorting (important to delete any sorting priorly set by Nova before adding the custom sorting)

    public static function indexQuery(NovaRequest $request, $query)
    {
        $query->select('posts.*', 'u.name');
        $query->join('users as u', 'posts.user_id', '=', 'u.id');

        $orderBy = $request->get('orderBy');
        if($orderBy == 'user') {
            $query->getQuery()->orders = null;
            $query->orderBy('u.name', $request->get('orderByDirection'));
        }

        return $query;
    }

[mateusgalasso]

Some solution?

[araeuchle]

For everybody who wants to achieve this you can use this package: https://github.com/TitasGailius/nova-search-relations. i had the same use case and figured out this was the best way to go as it is not possible in the core

[Cosmin-Parvulescu]

For everybody who wants to achieve this you can use this package: https://github.com/TitasGailius/nova-search-relations. i had the same use case and figured out this was the best way to go as it is not possible in the core

Does it preserve sort options?

[fico7489]

take a look at this package https://github.com/fico7489/laravel-eloquent-join
it should help you...

[josephxwf]

I really think this should be documented ;)

[FrittenKeeZ]

I added this to my App\Nova\Resource to enable BelongsTo sorting.

use Illuminate\Support\Str;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

/**
 * Apply any applicable orderings to the query.
 *
 * @param  \Illuminate\Database\Eloquent\Builder  $query
 * @param  array                                  $orderings
 * @return \Illuminate\Database\Eloquent\Builder
 */
protected static function applyOrderings($query, array $orderings)
{
    // Fix sort for BelongsTo relations.
    if (!empty($orderings)) {
        $orders = [];
        $model = static::newModel();
        foreach ($orderings as $property => $direction) {
            if (method_exists($model, Str::camel($property))) {
                $resource = __NAMESPACE__ . '\\' . Str::studly($property);
                if (class_exists($resource)) {
                    $relationship = $model->{Str::camel($property)}();
                    if ($relationship instanceof BelongsTo) {
                        $table = $relationship->getRelated()->getTable();
                        $query->leftJoin(
                            $table,
                            $relationship->getQualifiedForeignKeyName(),
                            '=',
                            $relationship->getQualifiedOwnerKeyName()
                        );
                        $orders[$table . '.' . $resource::$title] = $direction;
                    }
                }
            } else {
                $orders[$property] = $direction;
            }
        }
        $orderings = $orders;
        // Ensure proper fields are fetched.
        $query->select($model->getTable() . '.*');
    }

    // Default sorting.
    if (empty($orderings) && property_exists(static::class, 'orderBy')) {
        $orderings = static::$orderBy;
    }

    return parent::applyOrderings($query, $orderings);
}

[iHazzam]

Thanks

[davidhemphill]

There will likely be no plans to implement this feature.

[mfodor]

For everybody who wants to achieve this you can use this package: https://github.com/TitasGailius/nova-search-relations. i had the same use case and figured out this was the best way to go as it is not possible in the core

Does it preserve sort options?

Yes it does. Works perfectly for me.

[stephan-v]

Please don't just state it won't be implemented, but actually provide a reason as to why not.

[jonnywilliamson]

For everybody who wants to achieve this you can use this package: https://github.com/TitasGailius/nova-search-relations. i had the same use case and figured out this was the best way to go as it is not possible in the core

Thank you so much for that link. That helped me out massively!

Shame this isn't included in Nova as standard.

[rodrigopaco1986]

For those with the sort problem (in a lens in this case), you just need to add the alias name to your joined table(s), and disable the order prefix. I guess for resources it's something similar:

public static function query(LensRequest $request, $query) { return $request ->withoutTableOrderPrefix() ->withOrdering($request->withFilters( $query ->select(self::columns()) ->leftJoin('lead_debts', 'lead_debts.lead_id', '=', 'leads.id') ->where('vertical_id', LeadType::DEBT) ->where('affiliate_name', self::ESSENTIAL_DEBT_AFFILIATE_NAME) )); }

protected static function columns() { return [ 'leads.*', 'lead_debts.debt_amount as debt_amount' ]; }

Currency::make('Debt Amount', 'debt_amount') ->sortable(),