Yii CDbCriteria Join

Question

How I can write the query

SELECT *
  FROM doc_docs dd 
  JOIN doc_access da 
    ON dd.id=da.doc_id 
   AND da.user_id=7

with CDbCriteria syntax?

Answer 1

You actually cant completely write that since you have to apply the criteria to an activerecord model to obtain the primary table, but assuming you have a DocDocs model you can do it like this:

$oDBC = new CDbCriteria();
$oDBC->join = 'LEFT JOIN doc_access a ON t.id = a.doc_id and a.user_id = 7'; 

$aRecords = DocDocs::model()->findAll($oDBC);

Although it might be a lot easier if you give your DocDocs model a relation with doc_access, then you don't have to use the dbcriteria:

class DocDocs extends CActiveRecord
{
   ... 

   public function relations()
   {
      return array('access' => array(self::HAS_MANY, 'DocAccess', 'doc_id');
   }

   ...
}

$oDocDocs = new DocDocs;
$oDocDocs->id = 7;
$aRecords = $oDocDocs->access;

Should give you a fairly good idea how to start...