MYSQL Query to display meta values in a row

Question

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';

Barmar · Accepted Answer · 2021-12-07 22:06:02Z

3

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

answered Dec 7, 2021 at 22:06

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MYSQL Query to display meta values in a row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related