Skip to content

Instantly share code, notes, and snippets.

@htuscher
Created October 14, 2019 10:49
Show Gist options
  • Save htuscher/53073766c90c43b5393816bc1f8fd6be to your computer and use it in GitHub Desktop.
Save htuscher/53073766c90c43b5393816bc1f8fd6be to your computer and use it in GitHub Desktop.
Shopware Kibana Item Sales Query
SELECT
# Reference numbers and IDs
sod.id as orderDetailsID, sod.id as doc_id, NOW() as indexed_at, sod.orderID, sod.modus, sod.ordernumber, sod.articleordernumber, sod.name, cat.description as category, su.customergroup,
# Prices
sod.price, sod.quantity, (sod.price * sod.quantity) as total_price, sod.tax_rate, (sod.price / so.currencyFactor) as price_eur, (sod.price * sod.quantity / so.currencyFactor) as total_price_eur,
# Article status
sod_status_scs.`description` as item_order_status,
# Shipping
sod_shipping_scss.`description` as item_shipping_status, sod.shipped,
# Parent invoice
so_scs.`description` as order_status, so.ordertime, scp.name as payment_method, so.net, so.taxfree, so.currency, so.currencyFactor, so.subshopID, scs.name as shop_name, so.deviceType,
# Billing
sob.customernumber as billing_customernumber, sob.zipcode as billing_zip, sob.city as billing_city, scc_sob.countryname as billing_country, scc_sob.countryiso as billing_country_iso,
# Shipping
sos.zipcode as shipping_zip, sos.city as shipping_city, scc_sos.countryname as shipping_country, scc_sos.countryiso as shipping_country_iso,
# Article
sod.ean, soda.attribute1 as orderitem_attr1
FROM
s_order_details sod
# Order tables joined
LEFT JOIN s_order_details_attributes soda ON sod.id = soda.detailID
INNER JOIN s_order so ON sod.orderID = so.id
LEFT JOIN s_order_attributes soa ON so.id = soa.orderID
# Article category
LEFT JOIN (SELECT articleID, MIN(categoryID) as categoryID FROM s_articles_categories GROUP BY articleID) catId ON catId.articleID = sod.articleID
LEFT JOIN s_categories cat ON catId.categoryID = cat.id
# User
LEFT JOIN s_user su ON so.userID = su.id
# Shop name
LEFT JOIN s_core_shops scs ON so.subshopID = scs.id
# Payment info
LEFT JOIN s_core_paymentmeans scp ON so.paymentID = scp.id
# Order state
LEFT JOIN s_core_states so_scs ON so.status = so_scs.id
# Order state of article
LEFT JOIN s_core_detail_states sod_status_scs ON sod.status = sod_status_scs.id
# Shipping state of article
LEFT JOIN s_core_states sod_shipping_scss ON sod.shipped = sod_shipping_scss.id
# Billing info
LEFT JOIN s_order_billingaddress sob ON so.id = sob.orderID
LEFT JOIN s_core_countries scc_sob ON sob.countryID = scc_sob.id
# Shipping info
LEFT JOIN s_order_shippingaddress sos ON so.id = sos.orderID
LEFT JOIN s_core_countries scc_sos ON sos.countryID = scc_sos.id
# Article info
LEFT JOIN s_articles_details sad ON sod.articleordernumber = sad.ordernumber
LEFT JOIN s_articles_attributes saa ON sad.id = saa.articledetailsID
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment