Answers.Body: <p>The Order element query joins the billing address, and you can access the table with <code>billing_address</code> so something like this should work.</p>
  <p><code>Order::find()-&gt;andWhere(['billing_address.countryId' =&gt; 1])-&gt;all();</code></p>
  <p>The country IDs are stored in <code>commerce_countries</code> in commerce 3.</p>
  <p>This will also work in Commerce 4, but since the countries table is gone, you can get it by the country code:</p>
  <p><code>Order::find()-&gt;andWhere(['billing_address.countryCode' =&gt; 'AU'])-&gt;all();</code></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-12-29T01:19:22.347Z
Answers.Text: Would it be correct to have an array of countries in that query? 
  `Order::find()->andWhere(['billing_address.countryId' => [1, 2, 3] ])->all();` ?
Answers.UserDisplayName: cherrykoda
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-12-29T18:05:23.080Z
Answers.Text: Got it...`$orders = Order::find()->andWhere(['in', 'billing_address.countryId', $countries])->all();`
Answers.UserDisplayName: cherrykoda
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-12-28T07:24:50.793Z
Answers.LastActivityDate: 2024-01-02T05:19:02.533Z
Answers.LastEditDate: 2024-01-02T05:19:02.533Z
Answers.UserDisplayName: Luke Holder
Answers.Body: <p>Based on Luke's Reply, here's the end result based on ISO values</p>
  <pre><code> public static function getVatOrders()
      {
          $countryIds = [];
          $countriesISO = [
              'AT','BE','BG','ES','HR','CY','CZ','DK','EE','FI','FR','DE','GB','GR','HU','IE','IT','LV','LT','LU','MT','NL','PL','PT','RO','SE','SK','SI'
          ];
  
          foreach($countriesISO as $iso){
              $country = Commerce::getInstance()-&gt;getCountries()-&gt;getCountryByIso($iso);
              $id = $country-&gt;id;
              $countryIds[] = $id;
          }
  
          $orders = Order::find()-&gt;andWhere([
              'in',
              'billing_address.countryId',
              $countryIds
          ]);
  
          return $orders;
      }
  
  }
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-12-29T18:34:36.190Z
Answers.LastActivityDate: 2024-01-04T06:09:21.880Z
Answers.LastEditDate: 2024-01-04T06:09:21.880Z
Answers.UserDisplayName: cherrykoda
Body: <p>A little stuck on trying to get the query right for this, I have a module where I need to write a function that returns all orders placed with billing addresses in a selected list of countries. The <code>countryIso</code> field on the billingaddress is perfect, but Im not sure how to target it in an orders query.</p>
  <p>For context, this is a VAT reporting tool and needs to be based on billing country, not order adjustments.</p>
  
Tags: <plugin-craftcommerce><query><order><plugin-craftcommerce3>
Title: Return all orders from a list of countries in Commerce v3
ThreadText: # Return all orders from a list of countries in Commerce v3
  
  (Asked by: username_0 on 2023-12-28)
  
  A little stuck on trying to get the query right for this, I have a module where I need to write a function that returns all orders placed with billing addresses in a selected list of countries. The `countryIso` field on the billingaddress is perfect, but Im not sure how to target it in an orders query.
  
  For context, this is a VAT reporting tool and needs to be based on billing country, not order adjustments.
  
  **Tags:** plugin-craftcommerce, query, order, plugin-craftcommerce3
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2023-12-28. Score: 2 | Accepted answer
  
  The Order element query joins the billing address, and you can access the table with `billing_address` so something like this should work.
  
  `Order::find()->andWhere(['billing_address.countryId' => 1])->all();`
  
  The country IDs are stored in `commerce_countries` in commerce 3.
  
  This will also work in Commerce 4, but since the countries table is gone, you can get it by the country code:
  
  `Order::find()->andWhere(['billing_address.countryCode' => 'AU'])->all();`
  
  > **Comments:**
  > **username_0**: Would it be correct to have an array of countries in that query?  `Order::find()->andWhere(['billing_address.countryId' => [1, 2, 3] ])->all();` ?  
  > **username_0**: Got it...`$orders = Order::find()->andWhere(['in', 'billing_address.countryId', $countries])->all();`  
  
  ---
  
  ### Answer by username_0 on 2023-12-29. Score: 1
  
  Based on username_1's Reply, here's the end result based on ISO values
  
  ```
   public static function getVatOrders()
      {
          $countryIds = [];
          $countriesISO = [
              'AT','BE','BG','ES','HR','CY','CZ','DK','EE','FI','FR','DE','GB','GR','HU','IE','IT','LV','LT','LU','MT','NL','PL','PT','RO','SE','SK','SI'
          ];
  
          foreach($countriesISO as $iso){
              $country = Commerce::getInstance()->getCountries()->getCountryByIso($iso);
              $id = $country->id;
              $countryIds[] = $id;
          }
  
          $orders = Order::find()->andWhere([
              'in',
              'billing_address.countryId',
              $countryIds
          ]);
  
          return $orders;
      }
  
  }
  
  ```
  
  
  ---
  
  