Problem with searching for a relationship attribute

[lironesamoun]

Hi,

I would like to be able to search a has-many-relationship.

I have set up an users table and in this table, thank to the customized view, I was able to write all the groups that belong to an user.
Now, I would like to add the possibility to search for a specific group using the search box.
However, after following the documentation, I was not able to make the query works whereas on MySql, it is working properly.

row-user-table

<x-livewire-tables::bs5.table.cell>
    {{ $row->name }}
</x-livewire-tables::bs5.table.cell>

<x-livewire-tables::bs5.table.cell>
    {{ $row->email }}
</x-livewire-tables::bs5.table.cell>

<x-livewire-tables::bs5.table.cell>
    @foreach($row->groups as $group)
    <span class="badge badge-info mb-1">{{ $group->name }}</span>
    @endforeach
</x-livewire-tables::bs5.table.cell>

<x-livewire-tables::bs5.table.cell>
    {{ $row->email_verified_at }}
</x-livewire-tables::bs5.table.cell>

UsersTable component

<?php

namespace App\Http\Livewire\Admin;

use App\Models\User;
use Illuminate\Database\Eloquent\Builder;
use Livewire\Component;
use Rappasoft\LaravelLivewireTables\DataTableComponent;
use Rappasoft\LaravelLivewireTables\Views\Column;

class UsersTable extends DataTableComponent
{

    public function columns(): array
    {
        return [
            Column::make('Name')
                ->sortable()
                ->searchable(),
            Column::make('E-mail', 'email')
                ->sortable()
                ->searchable(),
            Column::make('Group')
                ->sortable(function (Builder $query, $direction) {
                    return $query->leftJoin('group_user', 'users.id', '=', 'group_user.id')
                        ->leftJoin('groups', 'groups.id', '=', 'group_user.group_id')
                        ->select('users.id', 'users.name', 'users.email', 'groups.name as group')
                        ->orderBy('group', $direction);
                })->searchable(),
            Column::make('Verified', 'email_verified_at')
                ->sortable(),
        ];
    }

    public function query(): Builder
    {
        return User::with('roles', 'groups')->when($this->getFilter('search'), fn ($query, $term) => $query->search($term));
    }

    public function rowView(): string
    {
        return 'tables.row-users';
    }

}

Searchable scope

    public function scopeSearch($query, $term)
    {
        return $query->where(
            fn ($query) => $query->leftJoin('group_user', 'users.id', '=', 'group_user.user_id')
                ->leftJoin('groups', 'groups.id', '=', 'group_user.group_id')
                ->select('users.id', 'users.name', 'users.email')
                ->where('users.name', 'like', '%' . $term . '%')
                ->orWhere('email', 'like', '%' . $term . '%')
                ->orWhere('groups.name', 'like', '%' . $term . '%')
        );
    }

The SQL generated works properly but when I search on the UI, I get the following error message

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'groups.name' in 'where clause' (SQL: select count(*) as aggregate from `users` where (`users`.`name` like %user% or `email` like %user% or `groups`.`name` like %user%) and (`users`.`name` like %user% or `users`.`email` like %user% or `users`.`group` like %user%))

Any ideas how to set up properly a search filter for a has-many-relationship ?

Thank you

[rappasoft]

You don't use both searchable on the columns and scopeSearch. Remove searchable from all your columns and scopeSearch will work, but you then need to add functionality to search the rest of your columns from the scope.

[lironesamoun]

I did that as well and it is still not working.
I have the error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'groups.name' in 'where clause' (SQL: select count(*) as aggregate from `users` where (`users`.`name` like %test% or `email` like %test% or `groups`.`name` like %test%))

I convert the query to SQL and it is working.
I have as well tried on Tinker and it is working as well

DB::table('users')->leftJoin('group_user', 'users.id', '=', 'group_user.user_id')->leftJoin('groups', 'groups.id', '=', 'group_user.group_id')->select('users.id', 'users.name as user_name', 'users.email', 'groups.*')->where('users.name', 'like', '%' . 'test' . '%')->orWhere('groups.name', 'like', '%' . 'test' . '%')->get()

  public function scopeSearch($query, $term)
    {

        return $query->where(
            fn ($query) => $query->leftJoin('group_user', 'users.id', '=', 'group_user.user_id')
                ->leftJoin('groups', 'groups.id', '=', 'group_user.group_id')
                ->select('users.id', 'users.name as user_name', 'users.email', 'groups.*')
                ->where('users.name', 'like', '%' . $term . '%')
                ->orWhere('email', 'like', '%' . $term . '%')
                ->orWhere('groups.name', 'like', '%' . $term . '%')
        );
    }

[lironesamoun]

I tried something else and it is working only after refreshing the page another time.

and it is working without the closure:

 public function scopeSearch($query, $term)
    {
        return $query->leftJoin('group_user', 'users.id', '=', 'group_user.user_id')
            ->leftJoin('groups', 'groups.id', '=', 'group_user.group_id')
            ->select('users.id', 'users.name as user_name', 'users.email', 'groups.*')
            ->where('users.name', 'like', '%' . $term . '%')
            ->orWhere('email', 'like', '%' . $term . '%')
            ->orWhere('groups.name', 'like', '%' . $term . '%');
    }

Any insights ?