Drupal Commerce order, line item and product export from MySQL

completedAnonymousOrdersFromLastYear.sql

SELECT
  commerce_order.order_id,
  FROM_UNIXTIME(commerce_order.created) AS created,
  GROUP_CONCAT(DISTINCT commerce_line_item.line_item_label) AS line_item_label,
  GROUP_CONCAT(DISTINCT commerce_product.type) AS productType,
  IF(
    GROUP_CONCAT(DISTINCT commerce_product.type) = "bundle",
    GROUP_CONCAT(DISTINCT bundleProduct.sku),
    GROUP_CONCAT(DISTINCT commerce_product.sku)
  ) AS productSKUList,
  IF(
    GROUP_CONCAT(DISTINCT commerce_product.type) = "bundle",
    GROUP_CONCAT(DISTINCT bundleProduct.title),
    GROUP_CONCAT(DISTINCT commerce_product.title)
  ) AS productTitleList,
  GROUP_CONCAT(DISTINCT commerce_order.mail) AS email,
  GROUP_CONCAT(DISTINCT shippingAddress.commerce_customer_address_first_name) AS firstName,
  GROUP_CONCAT(DISTINCT shippingAddress.commerce_customer_address_last_name) AS lastName,
  GROUP_CONCAT(DISTINCT shippingAddress.commerce_customer_address_organisation_name) AS organisationName,
  GROUP_CONCAT(DISTINCT shippingAddress.commerce_customer_address_postal_code) AS postalCode,
  GROUP_CONCAT(DISTINCT shippingAddress.commerce_customer_address_country) AS country,
  GROUP_CONCAT(DISTINCT shippingAddress.commerce_customer_address_locality) AS commerce_customer_address_locality,
  GROUP_CONCAT(DISTINCT shippingAddress.commerce_customer_address_thoroughfare) AS commerce_customer_address_thoroughfare,
  GROUP_CONCAT(DISTINCT shippingAddress.commerce_customer_address_premise) AS commerce_customer_address_premise,
  GROUP_CONCAT(DISTINCT shippingAddress.commerce_customer_address_administrative_area) AS commerce_customer_address_administrative_area
FROM commerce_order

-- Get line items and line items' products
LEFT JOIN field_data_commerce_line_items
  ON field_data_commerce_line_items.entity_id = commerce_order.order_id
LEFT JOIN commerce_line_item
  ON commerce_line_item.line_item_id = field_data_commerce_line_items.commerce_line_items_line_item_id
  AND commerce_line_item.order_id = commerce_order.order_id
LEFT JOIN commerce_product
  ON commerce_product.sku = commerce_line_item.line_item_label

-- For bundles, join to find the underlying product(s)
LEFT JOIN field_data_field_products
  ON commerce_product.type = 'bundle'
  AND field_data_field_products.entity_id = commerce_product.product_id
LEFT JOIN commerce_product bundleProduct
  ON bundleProduct.product_id = field_data_field_products.field_products_product_id

-- Get Shipping Address
LEFT JOIN field_data_commerce_customer_shipping
  ON field_data_commerce_customer_shipping.entity_id = commerce_order.order_id
LEFT JOIN commerce_customer_profile shippingProfile
  ON shippingProfile.profile_id = field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
LEFT JOIN field_data_commerce_customer_address shippingAddress
  ON shippingAddress.deleted = 0
  AND shippingAddress.entity_id = shippingProfile.profile_id

/*
Intentionally omitting Billing Address
-- Get Billing Address
LEFT JOIN field_data_commerce_customer_billing
  ON field_data_commerce_customer_billing.entity_id = commerce_order.order_id
LEFT JOIN commerce_customer_profile billingProfile
  ON billingProfile.profile_id = field_data_commerce_customer_billing.commerce_customer_billing_profile_id
LEFT JOIN field_data_commerce_customer_address billingAddress
  ON billingAddress.deleted = 0
  AND billingAddress.entity_id = billingProfile.profile_id
*/

WHERE
  commerce_order.UID = 0 -- Anonymous orders
  AND commerce_order.status = 'completed' -- Only paid in full orders
  AND FROM_UNIXTIME(commerce_order.created) > DATE_SUB(UTC_DATE(), INTERVAL 1 YEAR) -- Order was created within the last year
GROUP BY (commerce_order.order_id)
ORDER BY commerce_order.order_id DESC;