I have a hasMany relation declared in one of my models and I want to filter the result of my query.
Imagine I have a cart entity with CartModel declared and a order entity with OrderModel declared. I have declared a relation named orders (getOrders) in which we fetch orders of a cart. I want to fetch carts and it’s orders with the following condition. Order status should be ‘active’. My query looks like the following:
$carts = \app\models\Cart::find()->joinWith(‘orders’)->where([‘order.status’ => ‘active’])->all();
This should change to:
SELECT * FROM cart LEFT JOIN order ON order.cart_id=cart.cart_id WHERE order.status = ‘active’
However my ActiveRecord query fetches carts and all orders regardless of my condition.
What should I do to filter joinWith results?
P.S. I cannot declare onCondition since the condition part is generated dynamically.
softark
May 29, 2016, 10:13am
2
$carts = \app\models\Cart::find()->joinWith(‘orders’)->where([‘order.status’ => ‘active’])->all();
This should change to:
SELECT * FROM cart LEFT JOIN order ON order.cart_id=cart.cart_id WHERE order.status = ‘active’
However my ActiveRecord query fetches carts and all orders regardless of my condition.
This behavior is by design.
Yii uses the result set of the SQL above quoted to populate only the main models (Cart).
For the related models (Order), Yii executes another SQL, something like:
[sql]
select * from order where cart_id in (1,3,7,12, …)
[/sql]
where (1,3,7,12, …) is an array of cart ids retrieved in the 1st SQL.
Take a look at the following section of the guide:
Lazy Loading and Eager Loading
And you can read the practical HOW-TOs in the following wiki:
Drills : Search by a HAS_MANY relation in Yii 2.0
