CODEWITHSUNDEEP
joincriteriayii
Shafqat Jan
Shafqat Jan

Reputation: 75

I want to create a query with CDbCriteria with joins

SELECT * FROM table1 a, table2 m WHERE a.colid=1 and m.colid=1 order by a.date desc

Upvotes: 0

Views: 276

Answers (1)

Hearaman
Hearaman

Reputation: 8726

Assume your table structure is like below

        Table1
        ==========================
        colid | col2 | col3 | col4


        Table2
        ==========================
        colid | col2 | col3 | col4

Relationship is Important

In Yii framework ActiveRecords concept is performing huge role in database operations. An AR Class refers a database table. To perform relational queries you should define the relations ship between the tables. You can do this using relations() method in your models. Kindly observe below syntax.

If Table1 has more relational entities in Table2 than the relations ship should be as

 class Table1 extends CActiveRecord
 {
     public function relations()
     {
          return array(
              'table2' => array(self::HAS_MANY, 'Table2', 'colid'),
          );
     }
 }

Here, Table2 also has relationship with Table1. ie,

 class Table2 extends CActiveRecord
 {
     public function relations()
     {
          return array(
              'table1' => array(self::BELONGS_TO, 'Table1', 'colid'),
          );
     }
 }

If you have these instructions in your program now you are eligeble to perform relational queries.

Using CDbCriteria

    $cri = new CDbCriteria();
    $cri->join = 'JOIN table2 t2 ON t1.colid = 1 and t2.colid = 1';
    $cri->order = 'col3 DESC'; //In your case date fields

    $RS = Table1::model()->findAll($cri);

    echo '<pre>';
    print_r($RS);
    echo "</pre>";

    //to echo col2 info of table1
    echo $RS[0]->col2;

    //to echo col1 info of table2
    echo $RS[0]->table2[0]->col2;

You can do the same with one line

     $info = Table1::model()->findByPk(1);
     echo ($info->table2[0]->col2);

I hope it may help you to compute the instructions in your program.

Upvotes: 1

Related Questions