I have strange behavior in a query where I use the select strategy in a contain.
Basically, the query is made to get Sitedescription's content in the requested language if exists, if not, in english, if it doesn't exist, in French :
$query = $this->Sites->find()
->contain(
[
'Sitedescriptions' => [
'strategy' => 'select',
'queryBuilder' => function ($q) use ($reqLanguage_id, $reqLanguageFr_id, $reqLanguageEn_id) {
return $q
->select(['isGivenLanguage' => $q->newExpr()->eq('language_id', $reqLanguage_id), 'site_id', 'language_id')
->where (['language_id IN ' => [$reqLanguage_id, $reqLanguageFr_id, $reqLanguageEn_id]])
->order(['isGivenLanguage' => 'DESC', 'language_id' => 'DESC'])
->limit(1)
;
},
],
])
;
This one works just fine. Now I want to make te exact same query but I also want to get one more field from sitedescription, 'paragraph_0'. An existing field of course.
$query = $this->Sites->find()
->contain(
[
'Sitedescriptions' => [
'strategy' => 'select',
'queryBuilder' => function ($q) use ($reqLanguage_id, $reqLanguageFr_id, $reqLanguageEn_id) {
return $q
->select(['isGivenLanguage' => $q->newExpr()->eq('language_id', $reqLanguage_id), 'site_id', 'language_id', 'paragraph_0')
->where (['language_id IN ' => [$reqLanguage_id, $reqLanguageFr_id, $reqLanguageEn_id]])
->order(['isGivenLanguage' => 'DESC', 'language_id' => 'DESC'])
->limit(1)
;
},
],
])
;
Adding this field in select() makes the query to return an empty Sitedescription's array.
Query and outputs are given here https://gist.github.com/caBBAlainB/a721eebb3e8aa04a3506.
Any idea on the reason why the additional field is a problem?
EDIT Add generated sql queries for the second case, where paragraph_0 is requested:
With limit(1):
SHOW FULL COLUMNS FROM `sitedescriptions`
SHOW INDEXES FROM `sitedescriptions`
SELECT * FROM information_schema.key_column_usage AS kcu INNER JOIN information_schema.referential_constraints AS rc ON (kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME) WHERE kcu.TABLE_SCHEMA = 'modulwoomobtst' AND kcu.TABLE_NAME = 'sitedescriptions' and rc.TABLE_NAME = 'sitedescriptions'
SHOW TABLE STATUS WHERE Name = 'sitedescriptions'
SELECT (language_id = '1') AS `isGivenLanguage`, Sitedescriptions.id AS `Sitedescriptions__id`, Sitedescriptions.site_id AS `Sitedescriptions__site_id`, Sitedescriptions.language_id AS `Sitedescriptions__language_id`, Sitedescriptions.paragraph_0 AS `Sitedescriptions__paragraph_0` FROM sitedescriptions Sitedescriptions WHERE (Sitedescriptions.site_id in (63,64,827,828,838,841,849,859,866,908,1034,1116) AND language_id in (1,1,9)) ORDER BY isGivenLanguage DESC, language_id DESC
Without limit(1):
SHOW FULL COLUMNS FROM `sitedescriptions`
SHOW INDEXES FROM `sitedescriptions`
SELECT * FROM information_schema.key_column_usage AS kcu INNER JOIN information_schema.referential_constraints AS rc ON (kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME) WHERE kcu.TABLE_SCHEMA = 'modulwoomobtst' AND kcu.TABLE_NAME = 'sitedescriptions' and rc.TABLE_NAME = 'sitedescriptions'
SHOW TABLE STATUS WHERE Name = 'sitedescriptions'
SELECT (language_id = '1') AS `isGivenLanguage`, Sitedescriptions.id AS `Sitedescriptions__id`, Sitedescriptions.site_id AS `Sitedescriptions__site_id`, Sitedescriptions.language_id AS `Sitedescriptions__language_id`, Sitedescriptions.paragraph_0 AS `Sitedescriptions__paragraph_0` FROM sitedescriptions Sitedescriptions WHERE (Sitedescriptions.site_id in (63,64,827,828,838,841,849,859,866,908,1034,1116) AND language_id in (1,1,9)) ORDER BY isGivenLanguage DESC, language_id DESC
As far as I can see, both requests are the same but the result is different.