translate - CakePHP 3.4.7 - How to query translated content in contained associations?

Question

I upgraded cakephp to cakephp 3.4.7 version. My website is in multiple languages so an comment's and authors's title depends on the local. How to query translated content in contained associations? The php code in the controller looks like this:

//Comments belongsTo Authors     
$this->loadModel('Comments');
    $comments = $this->Comments->find('all')->where(['Comments.active' => 1])->contain([
    'Authors' => function ($q) {
       return $q
            ->where(['Authors.title LIKE' => '%'.$this->term.'%','Authors.active' => 1])
            ->order(['Authors.position' => 'ASC']);
     }
     ])->toArray();

This works only for the default language, but when I change the language, I get always an empty array. Table i18n contains records for 'comments' and 'authors' in other languages. In 'author' model:

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

When I changed the code according to the example:How to query translated content when using the translate behavior? I got the following results:

//Authors hasMany Comments - IT WORKS!!!
$this->loadModel('Authors');
$authors = $this->Authors->find('all')->where(['Authors.active' => 1])->contain([
'Comments' => function ($q) {
   return $q
        ->where(['Comments_title_translation.content LIKE' => '%'.$this->term.'%','Comments.active' => 1])
        ->order(['Comments.position' => 'ASC']);
 }
 ])->toArray();

//Comments belongsTo Authors  - IT DOES NOT WORK!!! 
$this->loadModel('Comments');
$comments = $this->Comments->find('all')->where(['Comments.active' => 1])->contain([
 'Authors' => function ($q) {
   return $q
        ->where(['Authors_title_translation.content LIKE' => '%'.$this->term.'%','Authors.active' => 1])
        ->order(['Authors.position' => 'ASC']);
 }
 ])->toArray();

In fact, my problem is second example //Comments belongsTo Authors The following error is displayed: Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Authors_title_translation.content' in 'on clause'

Answer 1

The problem is the order of joins being generated, it works for the hasMany association, as that association is being retrieved in a separate query, and the LIKE condition is being applied directly in the WHERE clause of that query.

In case of a belongsTo association, the associated table is being joined into the main query, and the conditions passed in the contain configuration are being applied in the joins ON clause, which happens before the join for the translation table is being defined, hence the error.

You can either apply the conditions on the main query instead:

$this->Comments
    ->find('all')
    ->where([
        $this->Comments->Authors->translationField('title') . ' LIKE' =>
            '%' . $this->term . '%',
        'Authors.active' => 1,
        'Comments.active' => 1
    ])
    ->contain([
        'Authors' => function ($q) {
            return $q->order(['Authors.position' => 'ASC']);
        }
    ])
    ->toArray();

or change to the select or the subquery strategy for fetching the associated data. In both cases the associated data will be retrieved in a separate query, and the conditions will be applied in its WHERE clause:

$this->Comments
    ->find('all')
    ->where(['Comments.active' => 1])
    ->contain([
        'Authors' => [
            'strategy' => CakeORMAssociation::STRATEGY_SELECT,
            'queryBuilder' => function ($q) {
                return $q
                    ->where([
                        $this->Comments->Authors->translationField('title') . ' LIKE' =>
                            '%' . $this->term . '%',
                        'Authors.active' => 1
                    ])
                    ->order(['Authors.position' => 'ASC']);
            }
        ]
    ])
    ->toArray();

As mentioned in the comments, you should in any case use the translate behaviors translationField() method to ensure that the correct field is being used depening on the currently set locale.

See also

• Cookbook > Database Access & ORM > Associations - Linking Tables Together > BelongsTo Associations
• Cookbook > Database Access & ORM > Behaviors > Translate > Querying Translated Fields