October: How to search on a model accessor in a list filter popup

[pavelmgn]

But filter by select work

Scope

worker:
        label: 'Ответственный'
        modelClass: Anplus\Andb\Models\Worker
        conditions: worker_id in (:filtered)
        nameFrom: fullName

Accessor

public function getFullNameAttribute()
    {
        return $this->surname . ' ' . $this->name . ' ' . $this->patronymic;
    }

[LukeTowers]

@pavelmgn That's because the search applies a search query to the results which interfaces with the database layer, not a collection of models. Feel free to provide a PR to change this behavior.

[robinbonnes]

Any updates on this? For now I'm using this but not ideal:

boot() {
        Event::listen(
            'backend.filter.extendQuery',
            function ($filter, $query, $scope) {
                if (post('scopeName', '') == 'user') {
                    $scope->nameFrom = 'name';
                }

                // Raise default query limit for long filters
                $query->limit(100000);
            }
        );
}

[mjauvin]

@robinbonnes can't you achieve this with a scope method defined in your list model and specifying the scope method name in your list filter's scope option?

[robinbonnes]

@robinbonnes can't you achieve this with a scope method defined in your list model and specifying the scope method name in your list filter's scope option?

Thanks for your reply. I don't think I can, because the scope method seems to be for the filter result query and doesn't affect the search param? https://octobercms.com/docs/backend/lists#filter-scope-options

Will search for a workaround...

[mjauvin]

@robinbonnes but isn't that what you're trying to do (modify the filter result query) ?

[robinbonnes]

@robinbonnes but isn't that what you're trying to do (modify the filter result query) ?

No scope function is like this:

    public function scopeFromUser($query, $value)
    {
        return $query->whereHas(
            'posts',
            function ($q) use ($value) {
                $q->whereIn('user_id', $value);
            }
        );
    }  

But we need to modify the search query, something like: concat(name, ' ', surname)

[robinbonnes]

Any updates on this? For now I'm using this but not ideal:

boot() {
        Event::listen(
            'backend.filter.extendQuery',
            function ($filter, $query, $scope) {
                if (post('scopeName', '') == 'user') {
                    $scope->nameFrom = 'name';
                }

                // Raise default query limit for long filters
                $query->limit(100000);
            }
        );
}

With this workaround I change the search query just before search, to search only on first name (name). But with this we can't search on surname.

[robinbonnes]

In this way its working:

config_filter.yaml:

scopes:
    user:
        label: User
        scope: fromUser
        nameFrom: full_name
        modelClass: RainLab\User\Models\User

Model:

public function scopeFromUser($query, $value)
{
    return $query->whereHas(
        'posts',
        function ($q) use ($value) {
            $q->whereIn('user_id', $value);
        }
    );
}

Plugin.php:

public function boot() {
    Event::listen(
        'backend.filter.extendQuery',
        function ($filter, $query, $scope) {
            if (post('scopeName', '') == 'user') {
                $scope->nameFrom = 'concat(name, " ", surname)';
            }
        }
    );
}

[mjauvin]

@robinbonnes you can achive the same with an accessor for full_name:

public function getFullNameAttribute()
{
    return $this->name . " " . $this->surname;
}

And you won't need the backend.filter.extendQuery handler in your last example

[LukeTowers]

@robinbonnes what exactly are you trying to do? Are you trying to make it so that a concatenated column result is included when list searches are run? In that case what I would recommend you do is that you add a new full_name column to columns.yaml that uses the select property, sets searchable: true and invisible: true. Now that column (even though it's not displayed) will be factored in when searching the list.

[mjauvin]

@LukeTowers this is for a list filter of type "group" which will show those result in a dropdown. He wants to show the concatenated values in the dropdown options.

[LukeTowers]

@mjauvin @robinbonnes ah, in that case this is an easy problem to solve. Just specify the options property to a method that builds your options array however you want it to instead of using nameFrom.

I.e.

scopes:
    user:
        label: User
        scope: fromUser
        options: getUserOptions
        modelClass: RainLab\User\Models\User

public function getUserOptions()
{
    $users = static::select('id', 'first_name', 'last_name')->getQuery()->get(); // to get an array instead of models for large results
    $result = [];
    foreach ($users as $user) {
        $result[$user['id']] = $user['first_name'] . ' ' . $user['last_name'];
    }
}

[mjauvin]

@LukeTowers I don't see the benefit, the code is more complex for no gain. I find it better to create an accessor as I suggested.

[robinbonnes]

@robinbonnes you can achive the same with an accessor for full_name:

public function getFullNameAttribute()
{
    return $this->name . " " . $this->surname;
}

And you won't need the backend.filter.extendQuery handler in your last example

That exact same accessor is already there and it works when opening the dropdown, but not when searching inside the dropdown.

[robinbonnes]

@mjauvin @robinbonnes ah, in that case this is an easy problem to solve. Just specify the options property to a method that builds your options array however you want it to instead of using nameFrom.

I.e.

scopes:
    user:
        label: User
        scope: fromUser
        options: getUserOptions
        modelClass: RainLab\User\Models\User

public function getUserOptions()
{
    $users = static::select('id', 'first_name', 'last_name')->getQuery()->get(); // to get an array instead of models for large results
    $result = [];
    foreach ($users as $user) {
        $result[$user['id']] = $user['first_name'] . ' ' . $user['last_name'];
    }
}

Didn't think about using the options parameter, thanks for that! Because User is a different plugin I'm using this solution for now, seems the cleanest:

public function boot()
{
    User::extend(
        function ($model) {
            $model->addDynamicMethod(
                'getUserOptions',
                function () {
                    return User::select('id', DB::raw("CONCAT(name, ' ', surname) as full"))->orderBy('full')->lists('full', 'id');
                }
            );
        }
    );
}

[mjauvin]

@robinbonnes be aware that CONCAT() does not work with SQLite (maybe others) in case the Database need to be changed at some point.

[bennothommo]

@robinbonnes @mjauvin we have a selectConcat method in v1.1.1 that is cross-compatible - https://github.com/octobercms/library/pull/434.