How to Search in Related Model (users) from a Livewire Table Without Using Aliases?

Hi everyone,

I’m using the Laravel Livewire Tables package, and I have a suscribers table that is related to a customers table, which in turn is related to a users table.

I want to display and search the subscriber’s name and surname (which are stored in users) directly from my Livewire DataTable without using aliases, because with aliases my table either doesn’t display or searching does not work.

This is what finally worked for me (at least the table renders and displays properly):

public function builder(): Builder
{
    return Suscriber::query()
        ->leftJoin('customers', 'suscribers.customer_id', '=', 'customers.id')
        ->leftJoin('users', 'customers.user_id', '=', 'users.id')
        ->select('suscribers.*', 'users.name', 'users.last_name', 'users.dni');
}

public function columns(): array
{
    return [
            Column::make(__("Name"), "customer.user.name")
            ->sortable()
            ->searchable(function (Builder $query, string $term) {
                $like = "%{$term}%";
                $query->where(function($q) use ($like) {
                    $q->where('users.name', 'like', $like)
                        ->orWhere('users.last_name', 'like', $like)
                        ->orWhereRaw("CONCAT_WS(' ', users.name, users.last_name) LIKE ?", [$like]);
                });
            }),
            Column::make(__("Surnames"), "customer.user.last_name")
            ->sortable()
            ->searchable(),
    ];
}

With this setup, the table works and displays the correct data as long as I don’t try to search.

The problem: When I try to search in the "Name" column, I get this SQL error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'vbc_customer.user.name' in 'where clause'

My question:

Is there any way to get the search working in this scenario, using the relation and without using aliases?

Is there a way to "map" the column for the table (so I can keep customer.user.name for display) but ensure the search query targets the correct column in the database (like users.name)?

Or should I switch the column to just name, even though that’s a bit confusing in my context?

Any tips, advice, or best practices are very welcome! Thank you in advance!

Alejo

11 months ago

Best Answer

Level 1

I solved it this way

   ->searchable(
                    function ($query, $searchTerm) {
                        $query->whereHas('customer.user', function ($query) use ($searchTerm) {
                            $query->where('name', 'like', '%' . $searchTerm . '%');
                        })->orWhereHas('customer.user', function ($query) use ($searchTerm) {
                            $query->where('last_name', 'like', '%' . $searchTerm . '%');
                        })->orWhereHas('customer.user', function ($query) use ($searchTerm) {
                            $query->whereRaw("CONCAT(name, ' ', last_name) LIKE ?", ["%{$searchTerm}%"]);
                        })->orWhereHas('customer.user', function ($query) use ($searchTerm) {
                            $query->whereRaw("CONCAT(last_name, ' ', name) LIKE ?", ["%{$searchTerm}%"]);
                        });
                    }
                )

Please or to participate in this conversation.