Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-10-21T13:20:34.467Z
Answers.Text: 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!
Answers.UserDisplayName: Simon Kuran
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-10-21T10:55:02.097Z
Answers.LastActivityDate: 2020-10-21T10:55:02.097Z
Answers.UserDisplayName: James Smith
Body: <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>
  
Tags: <plugin-development><plugin-craftcommerce><query><eager-loading>
Title: Eager load commerce order data in custom ElementExporter for Users
ThreadText: # 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!  
  
  ---
  
  