Active records by select query is not working correctly for some case.

Yii 2.0 General Discussions
1

Hi all,

I want display my rows in data provider (grid view) without remove dublicates.

Example:

Order::find()->joinWith(‘orderItems’);

public function getOrderItems() {

return $this->hasMany(OrderItems::className(), [‘order_id’ => ‘id’]);

}

And i want see all order items with order dublicates. But i see only one order with array order items.

Order::find()->joinWith(‘orderItems’)->indexBy(‘orderItems.id’) not work =(.

If i change Order on OrderItems:

OrderItems::find()->joinWith(‘order’);

orders with not items not displayed.

Thank you.

2

That is the expected result.




Order::find()->joinWith('orderItems')->...->one();

The code above will return an object of "Order" with an array of "OrderItem" objects, not an array of records of "Order joined with OrderItem".

It is the same with "all()".




Order::find()->joinWith('orderItems')->...->all();

This will return an array of "Order" objects, with each of them having an array of "OrderItem" objects related to it.

In other words, you should not expect ActiveRecord to have the result set of a sql like:




select * from order join order_item on order.id = order_item.order_id

  where order.id = <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/huh.gif' class='bbc_emoticon' alt='???' />

  order by <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/huh.gif' class='bbc_emoticon' alt='???' />

In fact, the sqls used to get the ActiveRecord result are like the following:




select order.* from order join order_item on order.id = order_item.order_id

  where order.id = <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/huh.gif' class='bbc_emoticon' alt='???' />

  order by <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/huh.gif' class='bbc_emoticon' alt='???' />

select * from order_item where order_item.order_id in (1,3,7,9,10, ...)

In the above, the 1st sql is used to get the Orders, while the 2nd query is used to get the related OrderItems. (1,3,7,9,10, … refer to the ids in the result of the 1st query.)

Although the 1st query is joining order_item table, only “order.*” will be used. Joining order_item has no meaning if you don’t use the columns of it to filter and/or sort the result of the Orders.