I'm looking for a more efficient way to write this query which displays meta values for an order in a single row. I can't think of a solution myself but before I redesign the database I wanted to see if anyone has any ideas to avoid 11 INNER JOINs. Here is my query which currently takes 20 seconds to load a single result (which is obviously not practical):
SELECT
o1.order_id,
o1.order_status AS 'Order Status',
m3.meta_value AS 'UOP Order Status',
m4.meta_value AS 'UOP Registration Date',
m5.meta_value AS 'UOP Banner Registration',
m6.meta_value AS 'UOP Banner Date',
m7.meta_value AS 'Course Completion Date',
m8.meta_value AS 'Course Grade',
m9.meta_value AS 'Grade Submission Date',
m1.meta_value AS 'First Name',
m2.meta_value AS 'Last Name',
o1.order_date AS 'Order Date',
m10.meta_value,
m11.meta_value,
o1.partner_id AS 'Source'
FROM partner_orders o1
INNER JOIN partner_order_meta m1
ON (o1.order_id = m1.order_id)
INNER JOIN partner_order_meta m2
ON (o1.order_id = m2.order_id)
INNER JOIN partner_order_meta m3
ON (o1.order_id = m3.order_id)
INNER JOIN partner_order_meta m4
ON (o1.order_id = m4.order_id)
INNER JOIN partner_order_meta m5
ON (o1.order_id = m5.order_id)
INNER JOIN partner_order_meta m6
ON (o1.order_id = m6.order_id)
INNER JOIN partner_order_meta m7
ON (o1.order_id = m7.order_id)
INNER JOIN partner_order_meta m8
ON (o1.order_id = m8.order_id)
INNER JOIN partner_order_meta m9
ON (o1.order_id = m9.order_id)
INNER JOIN partner_order_meta m10
ON (o1.order_id = m10.order_id)
INNER JOIN partner_order_meta m11
ON (o1.order_id = m11.order_id)
WHERE m1.meta_key = 'First Name' AND m2.meta_key = 'Last Name' AND m3.meta_key = 'uop_order_status' AND m4.meta_key = 'uop_registration_date' AND m5.meta_key = 'uop_banner_registration' AND m6.meta_key = 'uop_banner_registration_date' AND m7.meta_key = 'course_completion_date' AND m8.meta_key = 'course_grade' AND m9.meta_key = 'grade_submission_date' AND m10.meta_key = 'Course Number' AND m11.meta_key = 'Course Title';
CodePudding user response:
Use grouping:
SELECT
o1.order_id,
o1.order_status AS `Order Status`,
MAX(IF(m.meta_key = 'First Name', m.meta_value, NULL)) AS `First Name`,
MAX(IF(m.meta_key = 'Last Name', m.meta_value, NULL)) AS `Last Name`,
...
FROM partner_orders AS o1
JOIN partner_order_meta AS m ON o1.order_id = m.order_id
GROUP BY o1.order_id