Unable to run query based on value of translated field - need help please

Hi guys,

I have a UsersTable and a TagsTable associated via a BelongsToMany and it works fine - association is correct etc. The Tags table is using the Translate Behavior, it all works too. But, there is a but. When I run the query below and include a condition to base the query to the database on the translated field I get an error. It complains "SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘Tags_name_translation.content’ in ‘on clause’"

Can anyone one here see what is wrong with this query? I have been scratching my head for hours trying to figure out what I am doing wrong but all to no avail. Can someone on here help please? Thanks in advance for any help.

$query = $this->Users->find();
$query->matching('Tags', function ($tagsQuery) use ($tag) {
	$tagsTable = $this->Users->Tags;
	$tagsTranslationField = $tagsTable->translationField('name');
	unset($tagsTable);
	
	$conditions  = [
		'OR'=>[
			[
				'Tags.name LIKE'=> '%' . $tag . '%'
			],
			[
				$tagsTranslationField  . ' LIKE'=> '%' . $tag . '%'
			],
		],
	];
	
	return $tagsQuery->where($conditions)->distinct('Tags.id');
});

Behaviors on assocaitions are not going to be involved when using *matching() or *JoinWith(), they will only apply when using contain().

But even if they would apply here, your matching conditions are applied in the association join’s ON clause, which would come before the translation table could be joined in, so you’d still get the same error, and you’d have to move your conditions into the main query’s WHERE clause in order for that to work.

That being said, you’ll have to rethink how you filter things, you could for example use a subquery for filtering, ie you query the tags tabls directly with your translation field conditions, and use the results to filter the users query, something along the lines of this:

$filterQuery = $this->Users->Tags
    ->find()
    ->select('Users.id')
    ->distinct('Users.id')
    ->innerJoinWith('Users')
    ->where([
        'OR' => [
            [
                'Tags.name LIKE'=> '%' . $tag . '%'
            ],
            [
                $this->Users->Tags->translationField('name')  . ' LIKE' =>
                    '%' . $tag . '%'
            ],
        ],
    ]);

$usersQuery = $this->Users
    ->find()
    ->where([
        'Users.id IN' => $filterQuery,
    ]);

Here the translate behavior will apply on the filter query and add the required joins, so that you should end up with a query like this:

SELECT
    ...
FROM
    users Users
WHERE 
    Users.id IN (
        SELECT
            DISTINCT Users.id
        FROM
            tags Tags
        INNER JOIN
            tags_users TagsUsers ...
        INNER JOIN
            users Users ...
        LEFT JOIN
            i18n Tags_name_translation ...
        WHERE
            Tags.name LIKE ... OR Tags_name_translation.content LIKE ...
    )

Not the best performing query, but it should work.

@ndm Thank you for that. I simply could not figure out how to get around this problem in a pretty way so ended up with something similar to what you suggested. I am basically running a separate query, getting the needed results and then running another query based on that. Not pretty but it works and am happy, to compensate in terms of permance, I am caching the query so that should help.

$query->matching('Tags', function ($q) use ($tag) {
	$tag = urldecode($tag);
	$tagsTable = $this->Users->Tags;
	$tagTranslationField = $tagsTable->translationField('name');
	$tagTranslationSlugField = $tagsTable->translationField('slug');
	$tagConditions = [
		'OR'=>[
			[
				'Tags.name LIKE' => '%'. $tag . '%',
			],
			[
				'Tags.slug LIKE' => '%'. $tag . '%',
			],
			[
				$tagTranslationField .  ' LIKE' => '%'. $tag . '%',
			],					
			[
				$tagTranslationSlugField .  ' LIKE' => '%'. $tag . '%',
			],
		],
	];
	$tagCacheKey = md5(serialize($tagConditions));
	$associatedTags = $tagsTable->find()->select([
		'Tags.id',
		'Tags.name',
		'Tags.slug'
	])->where(
		$tagConditions
	)->cache(
		$tagCacheKey
	)->distinct(
		'Tags.id'
	)->all();

	unset(
		$tag, 
		$tagsTable, 
		$tagTranslationField,
		$tagTranslationSlugField,
		$tagConditions
	);

	$associatedTagsIds = $associatedTags->extract('id')->toArray();
	$associatedTagsIds = !empty($associatedTagsIds)?$associatedTagsIds:[0];
	return $q->where(['Tags.id IN'=>$associatedTagsIds])->distinct('Tags.id');
});