<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>

CC BY-SA 4.0
2023-12-29T01:19:22.347Z
Would it be correct to have an array of countries in that query? 
`Order::find()->andWhere(['billing_address.countryId' => [1, 2, 3] ])->all();` ?
cherrykoda
CC BY-SA 4.0
2023-12-29T18:05:23.080Z
Got it...`$orders = Order::find()->andWhere(['in', 'billing_address.countryId', $countries])->all();`
cherrykoda
CC BY-SA 4.0
2023-12-28T07:24:50.793Z
2024-01-02T05:19:02.533Z
2024-01-02T05:19:02.533Z
Luke Holder
<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>

CC BY-SA 4.0
2023-12-29T18:34:36.190Z
2024-01-04T06:09:21.880Z
2024-01-04T06:09:21.880Z
cherrykoda
<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>

<plugin-craftcommerce><query><order><plugin-craftcommerce3>
Return all orders from a list of countries in Commerce v3
# 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 Luke'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;
    }

}


---

