How to filter by translations of a belongsToMany association?

Question

I would like to query translations in an associated belongsToMany relationship. According to the docs and this question it should be possible to query in translations for associations. I tried the following (simplified) code:

    $result = $this->table()->find()
        ->where([
            $this->Activities->Tags->translationField('name') . ' LIKE' => 
                '%' . $request->filter . '%'
            ])
        ->leftJoinWith('Tags')
        ->contain(['Tags'])
        ->all()
        ->toArray();

Tags and Activities have a Many to many relationship.

Activities:

    $this->belongsToMany('Tags', [
        'foreignKey' => 'activity_id',
        'targetForeignKey' => 'tag_id',
        'joinTable' => 'activities_tags'
    ]);

    $this->addBehavior('Translate', ['fields' => ['name', 'description']]);

Tags:

    $this->belongsToMany('Activities', [
        'foreignKey' => 'tag_id',
        'targetForeignKey' => 'activity_id',
        'joinTable' => 'activities_tags'
    ]);

    $this->addBehavior('Translate', ['fields' => ['name']]);

ActivityTag:

    $this->belongsTo('Activities', [
        'foreignKey' => 'activity_id',
        'joinType' => 'INNER'
    ]);
    $this->belongsTo('Tags', [
        'foreignKey' => 'tag_id',
        'joinType' => 'INNER'
    ]);

However, I get the following generated SQL:

SELECT 
    ...
FROM `activities` `Activities` 
LEFT JOIN `activities_tags` `ActivitiesTags` ON `Activities`.`id` = (`ActivitiesTags`.`activity_id`) 
LEFT JOIN `tags` `Tags` ON `Tags`.`id` = (`ActivitiesTags`.`tag_id`) 
LEFT JOIN `i18n` `Activities_name_translation` ON (
    `Activities_name_translation`.`model` = :c0 
    AND `Activities_name_translation`.`field` = :c1 
    AND `Activities_name_translation`.`locale` = :c2 
    AND `Activities`.`id` = (`Activities_name_translation`.`foreign_key`)
) 
LEFT JOIN `i18n` `Activities_description_translation` ON (
    `Activities_description_translation`.`model` = :c3 
    AND `Activities_description_translation`.`field` = :c4 
    AND `Activities_description_translation`.`locale` = :c5 
    AND `Activities`.`id` = (`Activities_description_translation`.`foreign_key`)
) 
WHERE `Tags_name_translation`.`content` like :c6

Which leads me to the following error:

QLSTATE[42S22]: Column not found: 1054 Unknown column 'Tags_name_translation.content' in 'where clause'

The following join is missing:

LEFT JOIN `i18n` `Tags_name_translation` ON (
   `Tags_name_translation`.`model` = :c6 
    AND `Tags_name_translation`.`field` = :c7 
    AND `Tags_name_translation`.`locale` = :c8 
    AND `Tags`.`id` = (`Tags_name_translation`.`foreign_key`)
) 

Now my question/Edit:

What am I missing to configure CakePHP in order to generate the missing join? My intention is to filter Activities by translated Tags. It's working for non-translations.

Answer 1

As mentioned in the linked question, just like for contained hasMany associations, belongsToMany associations are being retrieved in a separate query, and this is where the Translate behavior would jump in and contain the translation associations (each field is being represented by a separate hasOne association), so that the translation table would be joined in.

The same is true for *joinWith() and *matching(), while it will apply joins and conditions on the main query, the actual association content and its related translations are again being retrieved in a separate query, ie the Translate behavior is not going to be involved in the main query, hence the translation table is not being joined in. One may call that a shortcoming of the ORM, maybe some kind of hook for joining/matching would be helpful where behaviors could modify queries accordingly, but for now there's no such thing.

So, without having put too much thought into it, you could for example use a correlated subquery (yeah, I know, it may not perform overly well) as the filtering condition, ie query the required tags via the Tags table, where the translations would be included, and use for example an EXISTS condition on Activities, something along the lines of this:

$tagsQuery = $this->Activities->Tags
    ->find()
    ->select(['id'])
    ->innerJoinWith('ActivitiesTags')
    ->where(function (\Cake\Database\Expression\QueryExpression $exp) use ($request)  {
        return $exp
            ->equalFields('ActivitiesTags.activity_id', 'Activities.id')
            ->like(
                $this->Activities->Tags->translationField('name'),
                '%' . $request->filter . '%'
            );
    });

$activitiesQuery = $this->Activities
    ->find()
    ->where(function ($exp) use ($tagsQuery) {
        return $exp->exists($tagsQuery);
    });

As can be seen, this will require to join in the join table (ActivitiesTags) manually (in earlier CakePHP versions you might need to add that association manually IIRC), so that you can match against ActivitiesTags.activity_id. The resulting query should look something like this:

SELECT 
  `Activities`.`id` AS `Activities__id`, ...
FROM 
  `activities` `Activities` 
WHERE 
  EXISTS (
    SELECT 
      `Tags`.`id` AS `Tags__id` 
    FROM 
      `tags` `Tags` 
      INNER JOIN `activities_tags` `ActivitiesTags` ON
        `Tags`.`id` = (`ActivitiesTags`.`tag_id`) 
      LEFT JOIN `i18n` `Tags_name_translation` ON (
        `Tags_name_translation`.`model` = 'Tags' 
        AND `Tags_name_translation`.`field` = 'name' 
        AND `Tags_name_translation`.`locale` = 'en_US' 
        AND `Tags`.`id` = (`Tags_name_translation`.`foreign_key`)
      ) 
    WHERE 
      (
        `ActivitiesTags`.`activity_id` = (`Activities`.`id`) 
        AND `Tags_name_translation`.`content` LIKE '%foobarbaz%'
      )
  )

There's most likely other ways to solve this, for example creating and containing the additional translation associations "manually" at Model.beforeFind time. Look at what TranslateBehavior::setupFieldAssociations() and TranslateBehavior::beforeFind() does, you'd have to apply something similar to your Activities table in order to achieve this.