Working with translated elements.

[DeadKat]

Hi @ypnos-web,

I have a simple question regarding text search in the datatable.

I have a datatable listing species, species have a translatable name and a latin name.

Let's say there's a species named "Long-beaked common dolphin" which latin name is "Delphinus capensis".

When i'm text searching dolphin notthing is found.

Should I make joins to find the translations in my method returning results or is there something included in the plugin ?

for now this is what i'm using in the controller method :

$this->DataTables->find('Species')->all()

Thank you again,

Stefan.

[ypnos-web]

How is the translation implemented? Is it in a different column of the table or in a secondary table / via a behavior? What is your datatables configuration (columns)?

You can join other tables via the 'contain' option as in the tutorial. You can use a column (joined or not) for searching by including it in columns with 'visible' => false, 'searchable' => true.

Another way is to set 'delegateSearch' option and then implement the search in your finder.

[DeadKat]

Translations are implemented with the i18n table, as specified in the cookbook.

I have a field "name" in the species table and all is made with the translateTrait.

I'm going to check the solutions you pointed.

Thank you.

[ypnos-web]

How did you end up solving the issue?

[DeadKat]

HI @ypnos-web ,

really sorry for not getting back at you with my solution yet, I'm late with the project including your plugin but as soon as i've finished i'll publish my works here to let you know.

For now it's just adding the joins and including conditions but it might change for something better if I can work it more.

Stefan.

[DeadKat]

Hi @ypnos-web ,

Here's what i've set up to work with translations. Translations are all set up in one tablme for all objects as described in the cookbook.

Te example features a simple model "Zones" which has its name translated :

Column definition :

[
            'title' => __('Name'),
            'name' => 'Zones_name_translation.content',
            'data' => '_translations.' . $current_locale . '.name',
            'width' => '15%',
        ],

$current_locale is a variable containing the locale currently used by the App ( in my case it can be en or fr ).

Controller method :

$data = $this->DataTables->find('Zones' , 'translations' , [
            'contain' => [
                'Structures'
            ]
        ])->group([
            'zones.id',
            'zones.name',
            'zones.modified',
            'zones.deleted',
            'zones_name_translation.id',
            'zones_name_translation.content',
            'zones_name_translation.locale',
            'zones_name_translation.model',
            'zones_name_translation.foreign_key',
            'zones_name_translation.field',
        ]);

        $this->set([
            'data' => $data,
            '_serialize' => array_merge($this->viewVars['_serialize'], ['data'])
        ]);

As your plugin executes a a finder it's easy to bind a translation finder which works directly without more coding than required.

Due to Postgresql restrictions on count function i had to specify each columns of the query in a group by clause , it can be really a pain if you link multiple models ( example a bit below ).

Here's an example featuring a model (Species) associated with more than one model :

$data = $this - > DataTables - > find('Species', 'translations', [
    'contain' => [
        'Groupes',
        'Caracteristics',
    ],
    'conditions' => $DTT_conditions,
]) - > group([
    'species.id',
    'species.latin_name',
    'species.name',
    'species.description',
    'species.modified',
    'species.deleted',
    'species.default_image',
    'species.groupe_id',
    'species.caracteristic_id',
    'species_name_translation.id',
    'species_name_translation.content',
    'species_name_translation.locale',
    'species_name_translation.model',
    'species_name_translation.foreign_key',
    'species_name_translation.field',
    'species_description_translation.id',
    'species_description_translation.content',
    'species_description_translation.locale',
    'species_description_translation.model',
    'species_description_translation.foreign_key',
    'species_description_translation.field',
    'groupes.id',
    'groupes.nom',
    'groupes.deleted',
    'groupes.modified',
    'groupes.caracteristic',
    'groupes.picto',
    'groupes.youngsters',
    'groupes.exact_counts',
    'groupes_nom_translation.id',
    'groupes_nom_translation.content',
    'groupes_nom_translation.locale',
    'groupes_nom_translation.model',
    'groupes_nom_translation.foreign_key',
    'groupes_nom_translation.field',
    'groupes_caracteristic_translation.id',
    'groupes_caracteristic_translation.locale',
    'groupes_caracteristic_translation.content',
    'groupes_caracteristic_translation.model',
    'groupes_caracteristic_translation.foreign_key',
    'groupes_caracteristic_translation.field',
    'caracteristics.id',
    'caracteristics.groupe_id',
    'caracteristics.name',
    'caracteristics.modified',
    'caracteristics.deleted',
    'caracteristics_name_translation.id',
    'caracteristics_name_translation.locale',
    'caracteristics_name_translation.content',
    'caracteristics_name_translation.model',
    'caracteristics_name_translation.foreign_key',
    'caracteristics_name_translation.field',
]);

Again i had to bind a group by due to postgresql but it would be nice to know if anyone encountered this problem on a mysql DB.

Hope it helps.

Stefan.

[DeadKat]

The code preview is a bit buggy or do i need to format it a particular way ? :(

[ypnos-web]

Thanks for the insights. Maybe we could add this to the wiki as an example/howto. I didn't get it though, what do you need a count for? Is it used in the translations finder?

I formatted your comment: For multiline code, use this markup:

´´´php

´´´

[DeadKat]

Thanks for the insight on the multiline code.

Regarding the count i was just supposing the results number shown in the datatable were coming from a count but i might be wrong.

Feel free to add these examples to the wiki :)

In fact when working with translations you just have to make sure you use the correct alias to display/filter the datatable.

[ypnos-web]

Yes, the DataTables helper uses count() on the Query (twice when filtering). With MySQL this leads to a simple COUNT(*) statement and nothing needs to be added to the query to make it work. See this: http://book.cakephp.org/3.0/en/orm/query-builder.html#query-count

[DeadKat]

Well this looks diferrent for Postgresql, a count with joins requires all fields to be in a group by clause.

I'm not used to Postgre, i have to work with it for a project.

[DeadKat]

I've just checked a bit stackoverflow and the postgresql documentation and as count is an agreggate function it requires a group by clause if there's other fields in the query.

this example work : select count(*) from my_schema.my_table

this one is not working : select column_a, count(*) from my_schema.my_table; but this one will : SELECT column_a, COUNT(*) FROM my_schema.my_table GROUP BY column_a;

Might be great to also put this in the wiki for postgre users.

Stefan.

[ypnos-web]

This sounds very much like how I learned SQL at university and is not bound to postgresql. I would assume it is the same for MySQL. Note however that this should not interfere with the use of $query->count(); (as done by this plugin).

See, here are examples of SQL that result by that use of count:

SELECT 
  (
    COUNT(*)
  ) AS `count` 
FROM 
  materials Materials 
  LEFT JOIN material_datas OrderingProperty4 ON (
    OrderingProperty4.material_id = Materials.id 
    AND OrderingProperty4.materialtypefield_id = '4270'
  ) 
  LEFT JOIN material_datas OrderingProperty5 ON (
    OrderingProperty5.material_id = Materials.id 
    AND OrderingProperty5.materialtypefield_id = '5507'
  ) 
  LEFT JOIN materialtypes Materialtypes ON Materialtypes.id = (Materials.materialtype_id) 
  LEFT JOIN units Units ON Units.id = (Materialtypes.unit_id) 
WHERE 
  (
    NOT (
      Materials.amount_left = 0 
      AND Materials.amount > 0
    ) 
    AND Materials.status = 'active' 
    AND materialtype_id = 1007
  )

and (filtered):

SELECT 
  (
    COUNT(*)
  ) AS `count` 
FROM 
  materials Materials 
WHERE 
  (
    NOT (
      Materials.amount_left = 0 
      AND Materials.amount > 0
    ) 
    AND Materials.status = 'active' 
    AND materialtype_id = 1007 
    AND Materials.id in (
      9777, 9778, 9779, 9780, 9781, 9782, 9783, 
      9784, 9785, 9786, 9787, 9788, 9789, 
      10596, 10597, 16535, 16670, 16736, 
      17016, 17073, 17356, 17525, 17070, 
      17075
    )
  )

Note that I have some custom stuff going on there, but the message is: It is a simple SELECT COUNT(*) FROM query.

So you might need to check again why you need the grouping in your case as it should not be needed for the simple record counting.

[DeadKat]

I think i've found what's going on with the group by, it looks like it happens with the limit method and does not make a problem with the count() method, maybe hydrate() ?.

//This works
$species = $this->Species->find('translations',[
            'contain' => [
                'Caracteristics'  => [
                    'finder' => 'translations',
                ],
                'Groupes' => [
                    'finder' => 'translations',
                ]
            ],
            'order' => 'Species_name_translation.content ASC'
        ])->limit(10);

$species->count();

// This does not work
$species->toArray();

I've just tested this in a REST call.

[ypnos-web]

That's a very good find! Could you use Debug console or debug($query) to obtain and post the SQL querys generated by these two runs? I wonder if Cake is doing something special here.