0

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.

Keith Power
  • 13,891
  • 22
  • 66
  • 135
fralbo
  • 2,534
  • 4
  • 41
  • 73
  • The first thing to do should be to inspect the queries that are being issued (check [**DebugKit**](http://book.cakephp.org/3.0/en/debug-kit.html)). Also is there any reason why you're not using one of the available translate behaviors? And are you aware that what you are doing there will leave you with only one single `sitedescriptions` record being selected for all fetched `sites` records, not _one_ for _every_ site, but really just _one_ for _all_ sites? – ndm May 25 '15 at 14:49
  • Hi @ndm. You surprise me! First, I can't explain all details here, but I cannot use translate behaviors for this purpose. But in fact you are right, the query doesn't return me what I expect because, yes, I would want one Sitedescription for every site. So, I first started to remove the limit(1) and miracle, just doing that makes Sitedescription to not be empty anymore AND, gets 'paragraph_0' to appear! How do you explain it? Secondly, can you tell me how to have just one for every site? Currently the one I need is the first one thanks to order(). – fralbo May 25 '15 at 18:37
  • Hi @ndm, I'm not so sure if I can use translate behavior or not in fact. I need more time to learn about it.... – fralbo May 26 '15 at 05:21
  • @ndm, any idea about the problem caused by limit(1)? – fralbo May 27 '15 at 04:55
  • Haven't had time yet to look into this further, sorry. `limit` however will get you nowhere, you'll have to make sure that all the necessary rows are being selected, as only a single additional query will be issued. I guess you won't get far this way, as selecting only the needed rows in a single query would be rather complicated. Just retrieving all associated descriptions and manually picking the needed ones at PHP level afterwards will most likely be easier. See also **http://stackoverflow.com/a/30270675/1392379** which is about a similar problem. You'd probably go for the manual approach. – ndm May 27 '15 at 11:51
  • @ndm, Thanks, I finally manualy picked up what I need, fairly easy and it works well like that. I will see maybe for better implementation when I'll have time. – fralbo May 27 '15 at 12:07

0 Answers0