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'), ); }

Developerium · Accepted Answer · 2013-11-26 10:48:18Z

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

Thanks for your help and please read my whole post because actually i want to create relations in my model so that i just directly get result in view Like $model->language_name, and i think it comes in array.
show me your relation from model you generated if your still stuck.

3 revs · Accepted Answer · 2014-01-03 07:36:27Z

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.

Collectives™ on Stack Overflow

How to get data from Relations with joins in yii

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related