FilamentF
Filament•2y ago
urbycoz

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');
    }


//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)
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(
                        '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.'%');
                            }
                        }
                    );
                }
Jump to solution
Was this page helpful?