Group similar meta_key values and create result set with meta_value data

Question

I have fields that look like this:

meta_key	meta_value
schedule_1_date	2021/2/7
schedule_1_lesson	100
schedule_2_date	2020/12/30
schedule_2_lesson	105
schedule_3_date	2021/2/8
schedule_3_lesson	90
schedule_4_date	2021/5/10
schedule_4_lesson	91

I want to select the data to meet the condition:

• meta_value of schedule_%_date is 2021/2/7 or 2021/2/8
• the number of '%' in schedule_%_lesson is equal to the number of '%' in the first condition

For example, with the condition above, I will get these data:

meta_key	meta_value
schedule_1_date	2021/2/7
schedule_1_lesson	100
schedule_3_date	2021/2/8
schedule_3_lesson	90

The code I've tried so far

SELECT * FROM 'table_name' WHERE (meta_key LIKE 'schedule_%_date' AND meta_value IN ('2021/02/07','2021/02/08')) OR (meta_key LIKE 'schedule_%_lesson')

Answer 1

Check this one out:

SELECT tt.meta_key AS lesson_key, tt.meta_value AS lesson_value,
  d.meta_key AS date_key, d.meta_value AS date_value
FROM `table_name` tt
JOIN
  (SELECT t.*, REPLACE(REPLACE(t.meta_key, 'schedule_', ''), '_date', '') AS key_num
  FROM `table_name` t
  WHERE t.meta_key LIKE '%schedule%date%'
    AND t.meta_value IN ('2021/2/7', '2021/2/8')) d
      ON d.key_num = REPLACE(REPLACE(tt.meta_key, 'schedule_', ''), '_lesson', '')
WHERE tt.meta_key LIKE '%schedule%lesson%'

It outputs data in a slightly different format, thought it might work for you as well:

lesson_key	lesson_value	date_key	date_value
schedule_1_lesson	100	schedule_1_date	2021/2/7
schedule_3_lesson	90	schedule_3_date	2021/2/8

This query though might not be optimal from indexes utilization perspective. Make sure to take this into consideration. Using % wildcard in the LIKE statements in the middle of search string reduces the performance of index.

Answer 2

You can use the REGEXP operator to filter for rows where the meta_key follow the pattern ^schedule_[0-9]+_date$. In a derived table also filter for the meta_value of such rows. Use replace() to remove the non-digit part from meta_key of that rows. Join that derived table to the actual table. In the ON clause you can build the wanted meta_keys using concat() and the digit part of the meta_keys from the derived table.

SELECT t1.meta_key,
       t1.meta_value
       FROM elbat t1
            INNER JOIN (SELECT replace(replace(meta_key,
                                               'schedule_',
                                               ''),
                                       '_date',
                                       '') n
                               FROM elbat t2
                               WHERE t2.meta_key REGEXP '^schedule_[0-9]+_date$'
                                     AND t2.meta_value IN ('2021/2/7',
                                                           '2021/2/8')) n
                       ON t1.meta_key IN (concat('schedule_', n.n, '_date'),
                                          concat('schedule_', n.n, '_lesson'));

db<>fiddle

Answer 3

I am going to assume that you don't have any use for the meta_key in your result set and that it is only used for grouping.

I find the table structure to be imperfectly designed for a pivot query, but with a few string trimming functions, a pivot can be used to simplify your result set (no subqueries are required).

Group on the characters before the second underscore in the meta_key column. Then use aggregate functions to isolate the desired values in your SELECT.

DB Fiddle

SELECT 
    MAX(IF(SUBSTRING_INDEX(meta_key, '_', -1) = 'date', meta_value, NULL)) `date`,
    MAX(IF(SUBSTRING_INDEX(meta_key, '_', -1) = 'lesson', meta_value, NULL)) `lesson`
FROM elbat
GROUP BY SUBSTRING_INDEX(meta_key, '_', 2)
HAVING `date` IN ('2021/2/7', '2021/2/8');

date	lesson
2021/2/7	100
2021/2/8	90