Yii2 activerecord unexpected results with limit in with() callback function

Question

I'm having a problem building a query with Yii2.

Let's say we have four related models: Customer, Order, Orderitem and Orderitempicture.

Order is related to Customer, Orderitem is related to Order, and Orderitempicture is related to Orderitem.

Below is the code:

Customers::find()
            ->where(['id' => $id])
            ->with(['orders' => function($q) {
                    $q->select(['id', 'name', 'customer_id', '(select COUNT(*) from orders where customer_id=order.id) AS thecount'])->having('thecount > 0');
                }])
            ->with(['orders.orderitems' => function($q) {
                    $q->select(['id', 'name', 'price', 'order_id']);
                }])
            ->with(['orders.orderitems.orderitemspictures' => function($q) {
                    $q->select(['id', 'src_thumb', 'orderitem_id'])->orderBy("id desc")->limit(1);
                }])
            ->select(['id'])
            ->asArray()
            ->one();

This is one of the executed statements from the debugger:

SELECT `id`, `src_thumb`, `orderitem_id` FROM `orderitempicture` WHERE `order_id` IN ('37', '42', '29', '36', '39', '41', '30', '40', '28', '38') ORDER BY `id` DESC LIMIT 1

Now my problem is in this this line: $q->select(['id', 'src_thumb', 'orderitem_id'])->orderBy("id desc")->limit(1);

What I am trying to do get one orderitempicture for each orderitem, however, what happens is that the SQL to get orderitempictures is merged into one statement, and the limit is applied to that statement, so the result is that I only get ONE orderitempicture for one orderitem, and I get nothing for the rest.

How can I set a limit to get one child for each parent? Is there a way to tell Yii to create multiple queries for each record in the parent and set a limit on that instead of creating one query?

you are using `one()`. so u always get one record from db or be specific. I really unable to understand what you are asking. — Insane Skull, Sep 29 '15 at 04:35
What I am trying to do is get ONE orderitempicture for EACH orderitem. What is happening is that I'm getting a total of ONE orderitempicture for ALL orderitems. — Rayan Salhab, Sep 29 '15 at 13:37

novice_developer · Answer 1 · 2015-10-16T17:20:01.710

0

I would like to clarify on how the one() - click here method can be applied. The one() should be applied to ActiveQuery instances where in the query returns one ActiveRecord, but depending on asArray(), this method can return an array.

My suggestion would be to use all() - click here. Since your query returns multiple records, it can be converted to an array of ActiveRecord instances using all().

edited Oct 16 '15 at 17:20

answered Oct 01 '15 at 02:43

novice_developer

131
1
2
12

1

I think you understand asArray() wrongly. You use it when you want the values to be returned as an array and not as ActiveRecord objects. You can have one record data returned as one array instead of one ActiveRecord, and you can have many records returned as an array of arrays instead of an array of ActiveRecords. – Rayan Salhab Oct 14 '15 at 19:35
I meant the same , edited the answer accordingly. What I was suggesting is that one() method can only be applied if the query returns exactly one row but in your case you are expecting multiple rows and applying one() to it which should have been all() – novice_developer Oct 16 '15 at 17:24

Yii2 activerecord unexpected results with limit in with() callback function

1 Answers1