Sophisticated condition

Hi all!

I need to make a complex condition, here is my goal:

I have three tables: order, product_order, product.

Order and product connected through product_order.

One order can have several products and one product can have several orders - i.e many to many.

(their relationship is described in product_order)

So my task:

  1. Select orders satisfying the condition (field "status" from "Order" = "number").

  2. For orders satisfies the "1)" find all records in the table "product_order".

  3. For all products which i got after "2)" add condition (field "in_shop" from "Product" = "1").

  4. And in the end I want to get all the orders for which related products through the table "product_order" satisfy the condition "3)".

What is important I need to get them through one condition.

I await your responses smart people=) If you can answer my question please write literature that would allow me to make such queries independently.

Assuming that U have relations:

order with order_product and order_product with product

Those relations should be something like:


‘orderProduct’ => [self::HAS_MANY , ‘OrderProduct’, [‘order_id’ => ‘order_id’]]


‘product’ => [self::BELONGS_TO, ‘Product’, 'product_id]

Than this should be an answer:

Order::model()->findAll([‘condition’=>‘t.status = “number” AND orderProduct.product.in_shop = 1’, ‘with’ => [‘orderProduct.product’], ‘together’ => true]);

Please check :

I already have relations like:

Order model: ‘products’ => array(self::MANY_MANY, ‘Product’, ‘product_order(order_id, product_id)’)

Product model: ‘orders’ => array(self::MANY_MANY, ‘Order’, ‘product_order(product_id, order_id)’)