How to get data from Relations with joins in yii

Question

I am beginner in Yii, So I am asking this question.

I have three different tables.

First Table

First table is language(id, language_name)

// id is primary key.

Second Table

Second Table is verse(id, topic_id, surah_id, verse_text)

// id is primary key,

Third Table

Third table is verse_translations(id, verse_id, language_id, translations_text)

// id is primary key, language_id is foreign key references with language table,

// verse_id is foreign key references with verse table.

Now My Question is.

I want to get the list of languages of available translations with specific verse_id. ? For that i want to make relations in verse model file that will return an available languages in my view, so how to get result in view also.? and what will be the changes in verse model, view and controller if any changes occur.

I have written MySQL query which is in below.

SELECT language.language_name from language 
Inner Join verse_translations ON  verse_translations.language_id = language.id
Where verse_translations.verse_id = 1

But i need this in Yii.

I have generated verse model through gii code generator.

My Verse Model Relations function.

public function relations()
{
 return array(
    'sorah' => array(self::BELONGS_TO, 'Sorah', 'sorah_id'),
    'topic' => array(self::BELONGS_TO, 'Topic', 'topic_id'),
    'verseFeedbacks' => array(self::HAS_MANY, 'VerseFeedback', 'verse_id'),
    'verseImages' => array(self::HAS_MANY, 'VerseImages', 'verse_id'),
    'verseLinks' => array(self::HAS_MANY, 'VerseLinks', 'verse_id'),
    'verseTafseers' => array(self::HAS_MANY, 'VerseTafseer', 'verse_id'),
    'verseTranslations' => array(self::HAS_MANY, 'VerseTranslations', 'verse_id'),
    'language_name' => array(self::HAS_MANY, 'Language', 'id'),
 );
}

Answer 1

I wrote you your sql code,

$result = Yii::app()->db->createCommand()
->select('l.language_name')
->from('language l')
->join('verse_translations vt' , 'l.id = vt.language_id ')
->join('verse v' , 'vt.id = v.id')
->where('v.id = :var' , array(':var'=>1))
->queryAll();

btw I didn't read all your post, just read your sql :D

UPDATE: if you define your relations in mysql before you generate model files, you get the relations generated for you.this is the easiest way possible, then you can do this:

$vers = Ver::model()->findByPk(1);
$allLangs = $vers->language_name; // this will give you an array of Language Model back

let me know what did

cheers

Answer 2

You can easily get the list of available translated languages from language table.

Let see first.

'verseTranslations' => array(self::HAS_MANY, 'VerseTranslations', 'verse_id'),

this relation will take all the rows of verse translation of specific verse id, mean if you have 10 different translation in 10 different languages with verse_id 1, it will display all. Now you can see in question verse_translation table have language_id.

So we can get all languages by that language_id.

Now we make another relation which is relating to language_id through verseTranslations, and this relation will display all the translated languages.

'verse_lang' => array(self::HAS_MANY, 'Language', array('language_id'=>'id'), 'through'=>'verseTranslations'),

So as i have written a Sql Query is equivalent to these two relations.

'verseTranslations' => array(self::HAS_MANY, 'VerseTranslations', 'verse_id'),
'verse_lang' => array(self::HAS_MANY, 'Language', array('language_id'=>'id'), 'through'=>'verseTranslations'),

On view, we can easily access it by var_dump($data->verse_lang)

That's it.

for understanding relations. You may read carefully to this link.

http://www.yiiframework.com/doc/guide/1.1/en/database.arr#relational-query-with-through

Hope it will help.

If you need any help then leave a message in comment box.

Thanks.