Can't filter on joined Tables

[alexonline82]

If i join tables, the initial output is ok. But if i wan't to add a filter, there comes the error message, that column can't be found or there more than one column with the same name (i.e. name). It looks like the filterin doesn't use the select fields.

Data Provider:

$query = new EloquentDataProvider(
        Contract::join('products', 'products.id', '=', 'contracts.product_id')
                ->join('vendors', 'vendors.id', '=', 'products.vendor_id')
                ->select('contracts.id',
                    'vendors.name as vendor_name',                        
                )
    );

Grid Initialization:

$grid = new Grid(
        (new GridConfig)
            ->setName('filter')
            ->setDataProvider($query)
            ->setColumns([
                (new FieldConfig)
                    ->setName('id')
                    ->setLabel('#')
                ,

                (new FieldConfig)
                    ->setName('vendor_name')
                    ->setLabel('Vendor')
                    ->addFilter(
                        (new FilterConfig)
                            ->setName('vendor_name')
                            ->setOperator(FilterConfig::OPERATOR_LIKE)
                    )                    
        ])

    );

Error message:

Column not found: 1054 Unknown column 'vendor_name' in 'where clause' (SQL: select count(*) as aggregate from `contracts` inner join `products` on `products`.`id` = `contracts`.`product_id`  inner join `vendors` on `vendors`.`id` = `products`.`vendor_id` where `vendor_name` like %Test%)

[Nayjest]

Probably you use mysql. Queries like

select name as vendor_name from vendors where vendor_name = ?

Is not correct for mysql. But it's generated when you trying to filter using aliased column.

There is few solutions how to organize such grid with filtering, one moment, I will find example

[Nayjest]

The simplest solution is to override filtering function:

(new FilterConfig)
    ->setName('vendor_name')
    ->setFilteringFunc(function($val, EloquentDataProvider $dp) {
        /** @var Illuminate\Database\Eloquent\Builder $builder */
        $builder = $dp->getBuilder();    
        $builder->where('vendors.name', 'like', $val);
    })

[Nayjest]

Upd: perhaps

$builder->where('vendors.name', 'like', "%$val%");

will be more cozily than

$builder->where('vendors.name', 'like', $val);

[Nayjest]

Also note than in last releases it's possible to build grids from php config:

Grids::make([
    'src' => Contract::join('products', 'products.id', '=', 'contracts.product_id')
                    ->join('vendors', 'vendors.id', '=', 'products.vendor_id')
                    ->select('contracts.id', 'vendors.name as vendor_name'),
    'columns' => [
           'id',
           [
                'some_other_column_name',
                'label'  => some_translate_function('Some Field'),
                'sortable' => true,
           ],
           [
               'name'=>'vendor_name',
               'filter' => [
                     'filtering_func' => ...
               ]
           ]  
    ],
    'components' => ...
]);

[Nayjest]

And in your code:

(new GridConfig)
            ->setName('filter')

"filter" -- little bit strange name for grid. As for filters, name must be equal to filtered column name if you not use custom filtering function using setFilteringFunc method.

[Nayjest]

Please give me know if issue is solved.

[alexonline82]

Thank you for the very fast answer. I will check all your advises on my code :-)

[alexonline82]

It works. Thank you for the great grid feature and the fast answer :-)