Laravel nova belongsTo filter

Question

I have one table championships and one table users. Also I have same models (championship and user).

In one championship may be one main judge, main secretary and judge operator. All of them also are roles in my application and stored in roles table.

I have created new a resource Championship with 3 belongsTo fields. All of them searchable.

        BelongsTo::make('Main judge', 'mainJudge', User::class)
            ->onFormsAndDetail()
            ->searchable(),

        BelongsTo::make('Main secreatary', 'mainSecretary', User::class)
            ->onFormsAndDetail()
            ->searchable(),


        BelongsTo::make('Judge operator', 'judgeOperator', User::class)
            ->onFormsAndDetail()
            ->searchable(),

During the search I need filter query in order to search give only users with same roles.

For example, during the search main judge I have to include next filter into query

select *
from users u
join users_roles ur on u.id = ur.user_id
join roles r on ur.role_id = r.id
where r.alias = 'judge'

I have found some methods into app/Nova/Resource.php such as relatableQuery but how can I use it inside User resource if I have 3 fields with User.

score 3 · Answer 1 · answered Oct 25 '18 at 08:12

3

on Championship resource you have to create function with name of relation.

e.g.

public static function relatableMainSecretary(NovaRequest $request, $query)
{
    return $query->where(...);
}

answered Oct 25 '18 at 08:12

Are you sure that relatable method I have to create on championship resource? Because associatableQueryMethod search relatable method on User model – Viktor Oct 25 '18 at 09:02

score 2 · Answer 2 · edited Oct 06 '20 at 15:52

The easiest (and unofficial) way to achieve this is to sniff how request URL is structured. With that in mind you can simply override relatableQuery method in your Nova/User class.

That's how it can be done, please keep in mind that strtolower function is optional and just saves unnecessary hassle with case sensitive typos.

public static function relatableQuery(NovaRequest $request, $query)
{
    $requestSegment = strtolower($request->segment(4));

    if ($requestSegment === 'mainjudge') {
        return $query->where(...);
    }

    if ($requestSegment === 'mainsecretary') {
        return $query->where(...);
    }

    if ($requestSegment === 'judgeoperator') {
        return $query->where(...);
    }
    
    return $query;
}

score 2 · Answer 3 · answered Nov 17 '22 at 01:55

Nova now has this as of 4.19.0 in the form of relatableQueryUsing(), which takes a closure parameter that allows you to modify the query building on the way through. This means you don't need to inspect the request slug when you relate the same model multiple times since it's doable on a per-field basis.

Docs: https://nova.laravel.com/docs/4.0/resources/relationships.html#relatable-query-filtering

In your specific case, would look something like this:

BelongsTo::make('Main judge', 'mainJudge', User::class)
    // THIS IS NEW
    ->relatableQueryUsing(function (NovaRequest $request, Builder $query) {
        // BUILD YOUR CONDITIONS HERE
        $query->where(...);
    })
    ->onFormsAndDetail()
    ->searchable(),

Based on the original SQL provided, you may also need to use some joins to get to the condition you want.

score 1 · Answer 4 · answered Aug 03 '21 at 11:52

You can modify query using https://nova.laravel.com/docs/3.0/resources/authorization.html#dynamic-relatable-methods

User.php resource

public static function relatableUser(NovaRequest $request, $query)
{
    if ($request->segment(4) === 'mainjudge' && $request->search) {
        return $query->where(); // your query
    }

    return  $query;
}

score 0 · Answer 5 · answered Sep 27 '22 at 12:30

This is if you want to use the current instance id

public static function relatableQuestionOptions(NovaRequest $request, $query)
{
    $resource = $request->newResourceWith($request->findModelQuery())->first();
    if(!$resource) {
        return;
    }
    return $query->where('exam_question_id', $resource->id);
}

Laravel nova belongsTo filter

5 Answers5