1

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;
}
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.

1
  • 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! Oct 21 '20 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.