CODEWITHSUNDEEP
sqlactiverecordyiiconditional-statementsrelation
Max Zhuravlev
Max Zhuravlev

Reputation: 334

How to bound primary key to custom ON condition in Yii Relations?

The problem:

How to bound primary key to custom relation query?

Context, what for:

One Source can relate to several different Modifications (MANY_MANY), each modification relate to some Product (BELONGS_TO). If several Products has one Source, it means that the Products are same - that's criteria. (I can't just merge same products, because it may turn out that they are not the same, but if I merge them - I can't split them back).

So, when I need to find all orders related to some product, I actually want to find all orders with same products, not only with current product.

Relation looks like this:

'orderedProducts'=>array(self::HAS_MANY,'OrderProduct','','on'=>('modification_id IN (
select DISTINCT ms2.modification_id FROM products p1
LEFT JOIN products_modifications pm ON pm.product_id = p1.product_id
LEFT JOIN modifications_sources ms ON ms.modification_id = pm.modification_id
LEFT JOIN modifications_sources ms2 ON ms2.source_id = ms.source_id
where p1.product_id='.$this->primaryKey.'
)')),

'orders'=>array(self::HAS_MANY,'Order',array('order_id'=>'order_id'),'through'=>'orderedProducts'),

$this->primaryKey is not working, It's here just to show where I need to bound primary key.

Any suggestions how to bound primary key there?

Upvotes: 1

Views: 2233

Answers (1)

Serj Zaharchenko
Serj Zaharchenko

Reputation: 2721

It is rather complicated query, and it would be much convenient to make this functionality by defining getter getOrderedProducts() for the model. Inside this getter, you can get primary key of your model simply by $this->primaryKey. Then you will be able to get your ordered products from your model by attribute $model->orderedProject, just as if you'd made it using relations. And, as some bonus, you can implement some caching for such heavy query.

As for the relation, my research performed by logging queries to SQL constructed when one accesses to the HAS_MANY relation field of the model shows that YII would bind primary key of the model to one of the named param :ypl0, :ypl1... etc. So, if you feel okay with some dirty hack, you may access to primary key of your model by bound param:

'orderedProducts'=>array(self::HAS_MANY,'OrderProduct','','on'=>('modification_id IN (
select DISTINCT ms2.modification_id FROM products p1
LEFT JOIN products_modifications pm ON pm.product_id = p1.product_id
LEFT JOIN modifications_sources ms ON ms.modification_id = pm.modification_id
LEFT JOIN modifications_sources ms2 ON ms2.source_id = ms.source_id
where p1.product_id=:ypl0'

but, you should make sure that YII would bind primary key to the first (:ypl0) of his params (that would be true, if you do not bind any params by yourself). Anyways, I rather recommend to rely on first approach by defining getter as more stable and customizable.

Upvotes: 1

Related Questions