1

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.

2 Answers 2

2

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();

2
  • Would it be correct to have an array of countries in that query? Order::find()->andWhere(['billing_address.countryId' => [1, 2, 3] ])->all(); ?
    – cherrykoda
    Commented Dec 29, 2023 at 1:19
  • Got it...$orders = Order::find()->andWhere(['in', 'billing_address.countryId', $countries])->all();
    – cherrykoda
    Commented Dec 29, 2023 at 18:05
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;
    }

}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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