How to write a join query with active record?

Hi,

I am struggeling with writing a ‘join’ query with active record.

Right now I am doing the following two queries but I believe this should be combined into one query:

$criteria=new CDbCriteria;

$criteria->select='amount, unitId';				

$criteria->condition='id=:productId';				

$criteria->params=array(':productId'=>$productId);				

$product=Products::model()->find($criteria);

					

$criteria=new CDbCriteria;					

$criteria->select='unitAbbreviation';					

$criteria->condition='id=:unitId';					

$criteria->params=array(':unitId'=>$product->unitId);					

$unit=Units::model()->find($criteria);

With the first query I get the amount and the unit ID for the specified product. The unit ID is then used in the second query to retrieve the unit abbreviation from the Units model.

The models have been built by gii. Foreign keys are set up between unit ID and the ID in the Units table. This shows up correctly in the Units model.

Now I have been unsuccessful in combining the above two queries into one query.

Could you give me an example on how you’d solve something similar

see this http://www.yiiframework.com/doc/api/1.1/CDbCriteria#join-detail

Yup, that’s the one I want to use. But I can not figure it out how to

something like this:

$criteria=new CDbCriteria;

$criteria->select='amount, unitId';   

$criteria->join='LEFT JOIN units ON units.id=products.id';                          

$criteria->condition='id=:productId';                           

$criteria->params=array(':productId'=>$productId);                              

$product=Products::model()->find($criteria);

That is what I have right now:

$criteria=new CDbCriteria;

$criteria->select='t.amount, t.unitId, units.unitAbbreviation';   

$criteria->join='LEFT JOIN units ON units.id=t.unitId';                          

$criteria->condition='t.id=:productId';                       	

$criteria->params=array(':productId'=>$productId);                              

$product=Products::model()->find($criteria);

However I can not seem to access unitAbbreviaton: $products->unitAbbreviation is empty;

have you double checked that it really has a value? try with inner join

Yes, I do get a value using the AR queries I submitted in my first post.

Even INNER JOIN does not get a value back.

But it seems like there is a different issue now. I get the error message: Products.unitAbbreviation is not defined.

Does this mean I have to add public $unitAbbreviation; to the Products model?

Or is there simply something wrong with the relations which I should check next?!

yes it means you have to add the property

I see. Do you know if this would also have to do add the property if I’d be using with like in the AR example? Or am I confusing with and join?

it will be the same when using with. you will have to define the property

How would I use with instead of join in the above example?

to use with you first have to declare the relation between the two tables in one of the models. Read more here http://www.yiiframework.com/doc/guide/1.1/en/database.arr#declaring-relationship

Then instead of the join issue:

$criteria->with='relation_name'

They have been declared using the model creator within gii. How would I translate the above query into a query using ‘with’?

Would i write this instead?

$criteria->with='units';

if units is the name of the relation in the model then yes this should work

But again, I’d would to declare the property in the model, right?

So I’d have to add:

public $unitAbbreviation;

Somehow this feels wrong