How to retrieve data from different tables and kee

Question

I am new in yii framework. I have three different tables in yii framework.

First Table

First table is language(id, language_name) // id is primary key.

Second Table

Second Table is verse(id,topic_id, verse_text) // id is primary key, topic_id is foreign 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.

How I write the query or use relation and etc that fetch the result by like in below given table.

verse_id | topic_id |    verse    |    verse_translation  |  language |

  1           1        verse here      translation here      English
                                       translation here      Spanish
                                       translation here      Japanese
                                       translation here      Italia

My Controller Method

public function actionVerse()
    {
    $topic_id = 1;  

  $result = Yii::app()->db->createCommand()->setFetchMode(PDO::FETCH_OBJ)
    ->select('v.id, v.verse_text, vt.translation_text, l.language_name as lname)
    ->from('verse v')
    ->join('verse_translations vt' , 'v.id = vt.verse_id')
        ->join('language l' , 'l.id = vt.language_id')
        ->where('t.id = :var' , array(':var'=>$topic_id))
        ->queryAll();

    $dataProvider=new CArrayDataProvider($result, array(
    'id'=>'Verse',
    'sort'=>array(
        'attributes'=>array(
             'id','verse_text','translation_text','lname'
        ),
    ),
    'pagination'=>array(
        'pageSize'=>10,
    ),));
        $this->render('myverse',array('dataProvider'=>$dataProvider));
    }

Any help will be appreciated.

Thanks.

Answer 1

Try this one

$count=Yii::app()->db->createCommand('SELECT COUNT(*) FROM tbl_user')->queryScalar();

$sql='SELECT * FROM tbl_user';

$dataProvider=new CSqlDataProvider($sql, array(
    'totalItemCount'=>$count,
    'sort'=>array(
        'attributes'=>array(
             'id', 'username', 'email',
        ),
    ),
    'pagination'=>array(
        'pageSize'=>10,
    ),
));
// $dataProvider->getData() will return a list of arrays.

Then you can pass your $dataProvider to your CGridView

Further docs on using custom query as dataProvider, check here.

Answer 2

If you want to work that way, it's much better to use relations.

If you call queryAll() or similar functions you'll always get an array.

If you need to have those relations in your DataProvider you should use the with() function fo the ActiveRecords, for example:

/**Controller **/
$model = new Translation('search');

/** Translation model **/
public function search(){
  ...
  $criteria->with = array('verse', 'language');
  ...
}

Either way, why are you trying to call it with -> and nos just an array? Are you assigning values anywhere? This should not be done in views.

Answer 3

For what you want you would probably be better using relations. See the Yii article about how to use them http://www.yiiframework.com/doc/guide/1.1/en/database.arr

This should provide you exactly what you need.

Answer 4

Fetching arrays is the expected behaviour. You can tell Yii to tell PDO to fetch objectc instead :

$result = Yii::app()->db->createCommand()->setFetchMode(PDO::FETCH_OBJ)
    ->select('v.id, v.verse_text, vt.translation_text, l.language_name as lname)
    ->from('verse v')
    ->join('verse_translations vt' , 'v.id = vt.verse_id')
        ->join('language l' , 'l.id = vt.language_id')
        ->where('t.id = :var' , array(':var'=>$topic_id))
        ->queryAll();

   foreach ($result as $row) {
       echo $row->id;
       echo $row->verse_text;
       echo $row->translation_text;

   }