CODEWITHSUNDEEP
phpsqlyii
J.K.A.
J.K.A.

Reputation: 7404

How to write complex JOIN query in CDbCriteria Yii Framework

I have two tables : product and document and both are in relations with document_id.

Now I want to fetch documents name by product_id from database using Yii CDbCriteria.

Like : 

SELECT d.* 
FROM `document` As d, `product_document` AS p 
WHERE d.document_id=p.document_id
AND
p.product_id=133;

I want to write same query but using CDbCriteria. I have tried it but I am getting stuck with its syntax.

Upvotes: 2

Views: 2256

Answers (2)

acorncom
acorncom

Reputation: 5955

Harry has the right syntax. If you want to make it cleaner and more reuseable throughout your app, consider using a named scope:

/* scope in your Document model */
public function forProductId($id) {
    $criteria = new CDbCriteria();
    $criteria->condition = 'product.id = :productId',
    $criteria->with = 'product';
    $criteria->params = array(
        ':productId' => (int)$id,
    );
    return $this->dbCriteria()->mergeWith($criteria);
}

Makes your setup far more reuseable over time ...

Upvotes: 5

HarryFink
HarryFink

Reputation: 1010

Here is your criteria, assuming that you have relations set up correctly:

$c = Document::model()->getDbCriteria();
$c->with[] = 'product';
$c->addCondition('product.product_id = :product_id');
$c->params[':product_id'] = 133;

Upvotes: 2

Related Questions