Need to search on fallback translation value too

[lorro]

Hi,

Would it be possible to let the datatable query for multiple fields for 1 specific datatable column? I have this columns array:

 'columns' => [
      ['title' => __('Name'), 'name' => 'ProductGroups_name_translation.content', 'data' => 'name'],
      ['title' => __('Brand'), 'name' => 'Brands.name', 'data' => 'brand.name']
  ]

But some productgroups don't get a match because they don't have a translated name in the active language. (In those cases the system should fall back to the name of the default language which is the field ProductGroups.name)

So what I would like to achieve is that I could supply multiple search columns for this "Name" datacolumn. The query should search for the search query in the following mysql fields:

ProductGroups_name_translation.content OR ProductGroups.name LIKE %search%

Is something like this possible? What I did now is just change 'name' => 'ProductGroups_name_translation.content' to 'ProductGroups.name' but that's not really the solution as products that do have a translated name will not have their translated value shown in the datatable :)

Thanks!

[lorro]

I wonder if this is the best solution, but it seems to work if I set it up like this. I created a hidden column with the fallback translation

['title' => __('Name'), 'name' => $name_field, 'data' => 'name'],
['title' => __('Fallback Name'), 'name' => 'ProductGroups.name', 'data' => 'name', 'visible' => false]

[ypnos-web]

I believe that adding a hidden column for global filter purposes is a simple and efficient solution.

If you need many columns, more flexibility, or local search, you can delegate the filtering to the model (your Table class) instead. This is how:

1. Set option delegateSearch to true in options array for DataTablesComponent find() call.
2. In your tables findAll() method (you may also use a custom finder method), read $options['globalSearch'] and/or $options['localSearch'] and add the conditions to the query accordingly.