Hi,
Is there any way to specify condition in declared relation like IF EXISTS some_record_with_param take param=:param1 ELSE take param=:param2 ? I have 2 tables in 1:n relation, first table describes record data, another one have language versions of text fields. What I wanna do is get text in active application language, but if not exists (may be only one transalted version for particular record) I want to get values in default language. Is there any way to do that?
did you had a look to i18n?
there is something explained like
Yii::app()->t('general', 'your text');
‘your text’ will only be translated, if there is a translation, otherwise ‘your text’ will be displayed.
No, that’s not my point. Text in my application is values, which are added by site administrator (articles/sites) in several different language versions, so that must be stored in the database. My point is to get data in actual language version if it exists, or default if not.
I found solution. I’m worring a little about performance of that… but it works. Modified relation:
'art_txts_lang' => array(self::HAS_ONE, 'ArticleTxt', 'artidx',
'condition'=>'language=:language OR language=:lang_main',
'params'=>array(':language'=>Glob::getLanguageId(), ':lang_main'=>0),
'group'=>'art_txts_lang.artidx, art_txts_lang.language DESC',
'order'=>'art_txts_lang.artidx',
'together'=>true
),
But I have a problem in CGridView with that solution. I looked into logs, and I saw that the COUNT() query before reading records is without GROUP BY relation query… That cause a problem with calculating records per page etc. Is there any way to force COUNT to use all relation conditions before read data to CGridView?
Problem wasn’t in count, but in too many fields in GROUP BY. Whatever.
Relation now looks like that:
'art_txts_lang' => array(self::HAS_ONE, 'ArticleTxt', 'artidx',
'select'=>'t.name, t.link, t.language',
'joinType'=>'RIGHT JOIN',
'condition'=>'art_txts_lang.language IN (:language, :lang_main)',
'params'=>array(':language'=>Glob::getLanguageId(), ':lang_main'=>0),
'group'=>'art_txts_lang.artidx DESC',
'order'=>'t.artidx',
'together'=>true,
),
… and still is the problem. In CGridView data render ok, I have what I want, if there is a current language version it displays, if not, default language displays. That is ok, if is LIMIT in generated query (like in CGridView), and if this limit value is less than all records count in ArticleTxt (all language versions). Othervise still it displays default language version:( I wish I could use that relation in frontend of my application too, but still is no effect…
I looked into generated query… Stg like that:
SELECT `data_tb`.`id` AS `id`, `text_tb`.`language` AS `language` , `text_tb`.`name` AS `name`
FROM `kcms_article` `data_tb`
RIGHT JOIN `kcms_article_txt` `text_tb` ON ( `text_tb`.`id` = `data_tb`.`id` )
WHERE (`text_tb`.`language` = 1 OR `text_tb`.`language` = 0)
GROUP BY `text_tb`.`id` DESC
ORDER BY `data_tb`.`id`
LIMIT 10
…and test it directly in phpMyAdmin with, and without LIMIT. Results:
With LIMIT 10:
id language name
1 1 testenglish
2 0 testes
3 0 artykułowy
4 1 ang
5 0 tytuł
6 0 cztery
7 0 długie
8 0 aaaaaaaaaa
9 1 socialtechniq
10 0 socjotechnika2
Without LIMIT:
id language name
1 0 test11
2 0 testes
3 0 artykułowy
4 0 pol
5 0 tytuł
6 0 cztery
7 0 długie
8 0 aaaaaaaaaa
9 0 socjotechnika
10 0 socjotechnika2
11 0 wprowadzenie
12 0 ssssss
13 0 czynnik ludzki
14 0 cccccccccc
15 0 ttttttttttttttt
16 0 mmmmm
I do not understand what that LIMIT changing…
I know, that is not directly Yii problem… but I’m out of ideas… maybe somebody has problem like that? How could I deal with that? Any idea to sort data (desc) in grouped records?
I’m tottaly stuck, have more than half application coded yet, and spend many hours on this problem, but do not see any solution.
If something is not clear in my description, please let me know, I explain everything.
To sorting data after grouping you need to join the table to itself. But i’m not sure it is possible with Active Records.
As concerns the strange behaviour of limit operator, I think it depends on DESC option in GROUP BY clause. It’s not syntax violation, but I dont know what such query shoud return.
Nothing change with LIMIT after removing DESC from GROUP BY clause. Still I don’t know what cause this behaviour, but I know if I deal with that, my problem will be solved.
Maybe somebody knows any other method to get that effect (read current language version record or default if there isn’t one - in relations)?
After many hours… solved 
Maybe it will be helpful for someone.
'art_txts_lang' => array(self::HAS_ONE, 'ArticleTxt', 'artidx',
'select'=>'name, link, language, content, meta_title, meta_keywords, meta_description',
'condition'=>'art_txts_lang.language=(SELECT MAX(language) FROM {{article_txt}} WHERE artidx=art_txts_lang.artidx AND language IN(:language, :language_default))',
'params'=>array(':language'=>Glob::getLanguageId(), ':language_default'=>BaseModel::DEFAULT_LANGUAGE),
'together'=>true,
),
I worried a little about performance of that, but still that is only one (as far as I find out) solution which works.
Ahh me and my non-typical problems… 