Filter table on joined table column

And if I run an Eloquent query outside of this filter, it works fine like this: (Tinkerwell)

DB::table('hosts')
->select('*')
->leftJoin('saldi',function( $query){
  $query->on('hosts.id','saldi.host');
})->where('saldi.assigned','>',2,false)
->ddRawSql();

The produced query is like:

"select * from `hosts` left join `saldi` on `host`.`id` = `saldi`.`host` where  `saldi`.`assigned` > 2"

Which is also a perfect query. And the following gives me the correct collection.

DB::table('hosts')
->select('*')
->leftJoin('saldi',function( $query){
  $query->on('hosts.id','saldi.host');
})->where('saldi.assigned','>',2,false)
->get();

Only, when I do this in the filter statement, no filtering is done...

Okay... found a solution to circumvent the bizarre way filament is skipping joined tables. Probably I get it wrong (or there is a bug) but I didn't find a normal way out. I think it's a strange approach but it works...

                Filter::make('Has guests')
                    ->query(
                        function($query){
                            $arr=[];
                            $out = DB::table('saldi')->where('assigned','>',0)->get();
                            foreach($out as $obj){
                                $arr[]=$obj->host;
                            }
                            return $query->whereIn('id',$arr);
                        }
                    ),
```

I faced the same situation recently and implementing the above solution was not practical for my purpose but there's another way to work around this limitation which I think is cleaner. I used Filament's baseQuery method to alter the main query and join on the tables I needed to join on.

As a developer, I'm unable to expose my employer's original code publicly (my code uses a custom filter form because I needed to have dependent selects in the form) but I've created a dummy/demo example using a common scenario of products which are assigned to categories.

public static function table(Table $table): Table
    {   
        return $table
            ->filters([
                Filter::make('filters')
                    ->indicateUsing(function (array $data): string {
                        $indicators = '';

                        if($data['category_id']){
                            $category = Category::find($data['category_id']);
                            return 'Category: ' . $category->name;
                        }
                        
                        return $indicators;
                    })
                    ->form([
                        Select::make('category_id')
                            ->label('Category')
                            ->options(function () {
                                $categories = Category::select('id', 'name')->orderBy('name', 'asc')->get();
                                return Arr::pluck($categories, 'name', 'id');
                            })
                            ->searchable(),
                    ])
                    ->columnSpanFull()
                    ->baseQuery(function (Builder $query, array $data): Builder {
                        //Join on the products to categories table 
                        if($data['category_id']){
                            $query->join('products_to_categories', 'products.id', '=', 'products_to_categories.product_id');
                        } 
                        return $query; 
                    })
                    ->query(function (Builder $query, array $data): Builder {
                        if($data['category_id']){
                            $query->where('products_to_categories.category_id', $data['category_id']);
                        } 
                        return $query;
                    })

            ])
            // ... put the rest of your table methods here, eg. columns etc
            ;
    }

I haven't tested it but using baseQuery() should also work without a custom form. Please see Filament's documentation (Filters > Getting Started > Modifying the base query).

NOTE: IF THE TABLES YOU JOIN ON CONTAIN AN ID COLUMN FOR THE PRIMARY KEY THE QUERY MAY RETURN THE ID COLUMN OF YOUR MAIN TABLE (PRODUCTS) PLUS THE TABLES YOU HAVE JOINED ON (PRODUCTS_TO_CATEGORIES) AND THE ID FOR EACH RECORD MAY THEN BE INCORRECT. THE FIX IS SIMPLE - SPECIFY THE COLUMNS YOU WANT IN THE BASEQUERY METHOD, EG.

$query->select('products.id', 'producst.name', 'products_to_categories.count') ..... etc