Filament•11mo ago

Searching in a table on a column using a polymorphic relationship

I have an Account model that has a Contact relation. The Contact relation can represent different entities: Customer or Employee.

//Account Model
    public function contact(): MorphTo
    {
        return $this->morphTo(__FUNCTION__, 'contactidtype', 'contactid');
    }

//Account Model
    public function contact(): MorphTo
    {
        return $this->morphTo(__FUNCTION__, 'contactidtype', 'contactid');
    }

//Customer & Employee Models
    public function account(): MorphOne
    {
        return $this->morphOne(Account::class, 'contact', 'contactidtype', 'contactid');
    }

//Customer & Employee Models
    public function account(): MorphOne
    {
        return $this->morphOne(Account::class, 'contact', 'contactidtype', 'contactid');
    }

Customers has a field named cust_name. Employees has a field named emp_name. I'm trying to implement a search functionality on the Account model that allows searching for accounts based on the name, which is created in a combinedName aggregated attribute. Is there a way to solve this without changing the database tables themselves? The below code doesn't work because there's not an emp_name column in the Customers table no and no cust_name field in the Employees table.

TextColumn::make('combinedName')
                ->searchable(query: function (Builder $query, string $search): Builder {
                   return $query->where(function ($query) use ($search) {
                          $query->where('contact_type', 'Customer')
                              ->whereHas('contact', function (Builder $q) use ($search) {
                                      $q->where('cust_name', 'ILIKE', "%{$search}%");
                               });
                          })
                         ->orWhere(function ($query) use ($search) {
                               $query->where('contact_type', 'Employee')
                                ->whereHas('contact', function (Builder $q) use ($search) {
                                    $q->where('emp_name', 'ILIKE', "%{$search}%");
                                });
                           })
                }, isIndividual: true)

TextColumn::make('combinedName')
                ->searchable(query: function (Builder $query, string $search): Builder {
                   return $query->where(function ($query) use ($search) {
                          $query->where('contact_type', 'Customer')
                              ->whereHas('contact', function (Builder $q) use ($search) {
                                      $q->where('cust_name', 'ILIKE', "%{$search}%");
                               });
                          })
                         ->orWhere(function ($query) use ($search) {
                               $query->where('contact_type', 'Employee')
                                ->whereHas('contact', function (Builder $q) use ($search) {
                                    $q->where('emp_name', 'ILIKE', "%{$search}%");
                                });
                           })
                }, isIndividual: true)

Solution:

Well what do you know? Turns out it was the right approach after all. ```TextColumn::make('contact.combinedName') ->searchable(query: function (Builder $query, string $search): Builder { return $query->whereHasMorph(...

Jump to solution

3 Replies

Brian Kidd•11mo ago

Check this section of the Laravel docs and see if this helps: https://laravel.com/docs/10.x/eloquent-relationships#querying-morph-to-relationships

Laravel - The PHP Framework For Web Artisans

Laravel is a PHP web application framework with expressive, elegant syntax. We’ve already laid the foundation — freeing you to create without sweating the small things.

urbycozOP•11mo ago

Thanks. I can't see any way to incorporate any of the methods or approaches there. I think my scenario is slightly different to the one they were designed for.

Solution

urbycoz•11mo ago

Well what do you know? Turns out it was the right approach after all.

TextColumn::make('contact.combinedName')
                ->searchable(query: function (Builder $query, string $search): Builder {
                    return $query->whereHasMorph(
                        'contact', 
                        [Customer::class, Employee::class],
                        function ($query, $type) use ($search) {
                            if ($type === 'Customer') {
                                $query->where('cust_name', 'like', '%'.$search.'%');
                            } elseif ($type === 'Employee') {
                                $query->where('emp_name', 'like', '%'.$search.'%');
                            }
                        }
                    );
                }

TextColumn::make('contact.combinedName')
                ->searchable(query: function (Builder $query, string $search): Builder {
                    return $query->whereHasMorph(
                        'contact', 
                        [Customer::class, Employee::class],
                        function ($query, $type) use ($search) {
                            if ($type === 'Customer') {
                                $query->where('cust_name', 'like', '%'.$search.'%');
                            } elseif ($type === 'Employee') {
                                $query->where('emp_name', 'like', '%'.$search.'%');
                            }
                        }
                    );
                }

Gaming

Programming

Searching in a table on a column using a polymorphic relationship