Querying and Filtering WP custom post type joined with meta

i just tried out to speed up my queries in WordPress. At the moment I have a custom post type called ‘schulungstermin’ (course date in english). With the advanced custom fields plugin I hooked in there some custom fields for begin date, end date etc. I now want to query these post type based on a training relationship field without the wp_query function because its damn slow.

My first try was to query all course date’s like this:

SELECT wp.ID,wp.post_title,

MAX(CASE WHEN  wm.meta_key = 'training' THEN wm.meta_value ELSE NULL END) AS training, 
MAX(CASE WHEN wm.meta_key = 'kurs_abgesagt' THEN wm.meta_value ELSE NULL END) AS kurs_abgesagt, 
MAX(CASE wm.meta_key WHEN 'beginn' THEN wm.meta_value ELSE NULL END) AS beginn, 
MAX(CASE WHEN wm.meta_key = 'ende' THEN wm.meta_value ELSE NULL END) AS ende, 
MAX(CASE WHEN wm.meta_key = 'max_teilnehmeranzahl' THEN wm.meta_value ELSE NULL END) AS max_teilnehmeranzahl, 
MAX(CASE WHEN wm.meta_key = 'registration_link' THEN wm.meta_value ELSE NULL END) AS registration_link 

FROM `dh201573z4hnbsg_posts` wp 
INNER JOIN `dh201573z4hnbsg_postmeta` wm ON (wm.`post_id` = wp.`ID`) 
WHERE wp.post_type='schulungstermin' GROUP BY wp.ID

This worked really well i got all my data like this:

enter image description here

Now I want to filter this list by the column training. For example where training LIKE ‘%”78″%’ (for the training with the ID 78). But I don’t know how this works on the MAX() generated tables. If I try to filter like:

MAX(CASE WHEN  wm.meta_key = 'training' AND wm.meta_value LIKE '%"78"%'THEN wm.meta_value ELSE NULL END) AS training, 

It doesn’t work because it skips the other columns (like kurse_abgesagt or beginn)completely, every column but training is NULL then.

Do you have any clue to get out of this a working, fast query?

regards and thanks in advance,

cheers


Source: wpdb

Leave a Reply