SQL Inspiration

Inspired by Raj KB I planned for weeks to write about one or the other SQL query I write during the day but I didn’t find time, but here is one!

We are currently preparing the start for a festival for one of our customers. One part is to support the swag sale and we want to reuse the ticket functionality to create pickup slips.

First step was to get all the invoices and so I created this beauty which takes care of the pitfalls of Shopware database like auto_increment on orders and draft versions. As well as utilises the cool new JSON functions MySQL 8 provides:

SELECT
	order_number,
	GROUP_CONCAT(ol.label, '##', ol.payload->"$.options", '##' SEPARATOR '|')
FROM
	`order` o
	INNER JOIN order_delivery od ON od.order_id = o.id
	INNER JOIN order_transaction ot ON ot.order_id = o.id
	INNER JOIN order_line_item ol ON ol.order_id = o.id
	
WHERE
	# Shipping is Click & Collect
	od.shipping_method_id = X'018fec7999999999a3c657850470dba0'
	# Payment Status paid
	AND ot.state_id = X'02D0E8A451A24999999999AB7C68637D'
	# Get newest version
	AND `auto_increment` = (
		SELECT
			MAX(`auto_increment`)
		FROM
			`order` o2
		WHERE
			o2.id = o.id)
		# get only live version, not edit drafts
		AND o.version_id = X'0fa91ce3e96a4bc2be4bd9ce752c3425'
		AND od.version_id = X'0fa91ce3e96a4bc2be4bd9ce752c3425'
		AND ot.version_id = X'0fa91ce3e96a4bc2be4bd9ce752c3425'
		AND ol.version_id = X'0fa91ce3e96a4bc2be4bd9ce752c3425'
		GROUP BY o.id, o.order_number
		
		LIMIT 1000

The result is a list of orders with a second column containing the products and their options in a parseable format.