<p>Your code looks like it actually has a further problem that you're only fetching the first order for a given user. Rather than eager loading (which is basically creating joins so that the relevant data is fetched in one go), why not use a hash map to look up each customer's set of orders? So, outside your users loop, create a separate query to fetch all orders and use GROUP BY to group them by user id. You can then reference each sub-array by its user id inside the users loop.</p>

CC BY-SA 4.0
2020-10-21T13:20:34.467Z
This site has a unique design and users are only allowed to make one purchase, ever, so fetching the first order isn't a mistake. I do like your idea of querying all orders and then matching them to the $user each loop. I think that could work. However, the relationship between users and orders is stored in the commerce_customers table. Orders do not directly have a user ID associated with them. Do you have any code examples of how this could be built? Thanks!
Simon Kuran
CC BY-SA 4.0
2020-10-21T10:55:02.097Z
2020-10-21T10:55:02.097Z
James Smith
<p>I have a custom ElementExporter for users and I'd like to include commerce order data. The below code works, but it's a ton of queries. I don't have a ton of experience with SQL queries, how can this be optimized with eager loading?</p>
<pre><code>public function export(ElementQueryInterface $query): array
{
    $results = [];

    /** @var ElementQuery $query */

    foreach ($query-&gt;each() as $user) {
        /** @var User $user */

        $orderTotal = '';
        $orderDate = '';
        $order = Order::find()
            -&gt;user($user)
            -&gt;hasPurchasables('41599')
            -&gt;orderBy('dateOrdered desc')
            -&gt;dateOrdered('&gt;= 2020-09-01')
            -&gt;isCompleted(true)
            -&gt;one();

        if ($order) {
            $orderTotal = '$' . $order-&gt;totalPrice;
            $orderDate = $order-&gt;dateOrdered-&gt;format('m/d/Y h:iA T');
        }

        $results[] = [
            'Donation Amount' =&gt; $orderTotal ?? '',
            'Donation Date' =&gt; $orderDate ?? '',
        ];
    }

    return $results;
}
</code></pre>

<plugin-development><plugin-craftcommerce><query><eager-loading>
Eager load commerce order data in custom ElementExporter for Users
# Eager load commerce order data in custom ElementExporter for Users

(Asked by: username_0 on 2020-10-20)

I have a custom ElementExporter for users and I'd like to include commerce order data. The below code works, but it's a ton of queries. I don't have a ton of experience with SQL queries, how can this be optimized with eager loading?
public function export(ElementQueryInterface $query): array
{
    $results = [];

    /** @var ElementQuery $query */

    foreach ($query->each() as $user) {
        /** @var User $user */

        $orderTotal = '';
        $orderDate = '';
        $order = Order::find()
            ->user($user)
            ->hasPurchasables('41599')
            ->orderBy('dateOrdered desc')
            ->dateOrdered('>= 2020-09-01')
            ->isCompleted(true)
            ->one();

        if ($order) {
            $orderTotal = '$' . $order->totalPrice;
            $orderDate = $order->dateOrdered->format('m/d/Y h:iA T');
        }

        $results[] = [
            'Donation Amount' => $orderTotal ?? '',
            'Donation Date' => $orderDate ?? '',
        ];
    }

    return $results;
}

**Tags:** plugin-development, plugin-craftcommerce, query, eager-loading

---

## 1 answers

### Answer by username_1 on 2020-10-21. Score: 1

Your code looks like it actually has a further problem that you're only fetching the first order for a given user. Rather than eager loading (which is basically creating joins so that the relevant data is fetched in one go), why not use a hash map to look up each customer's set of orders? So, outside your users loop, create a separate query to fetch all orders and use GROUP BY to group them by user id. You can then reference each sub-array by its user id inside the users loop.

> **Comments:**
> **username_0**: This site has a unique design and users are only allowed to make one purchase, ever, so fetching the first order isn't a mistake. I do like your idea of querying all orders and then matching them to the $user each loop. I think that could work. However, the relationship between users and orders is stored in the commerce_customers table. Orders do not directly have a user ID associated with them. Do you have any code examples of how this could be built? Thanks!  

---

