0

I am trying to get custom posts using $wpdb->get_results() as follows:

SELECT p.ID, p.post_content, p.post_title, p.post_excerpt, 
pm1.meta_value as meal_date, 
(case when pm2.meta_key is null then '' else pm2.meta_value end) as meal_plan,
t.name as meal_type 
FROM wp_posts AS p 
LEFT JOIN wp_term_relationships AS r ON (p.ID = r.object_id)
INNER JOIN wp_term_taxonomy AS x ON (r.term_taxonomy_id = x.term_taxonomy_id)
INNER JOIN wp_terms AS t ON (r.term_taxonomy_id = t.term_id)
INNER JOIN wp_postmeta As pm1 ON (p.ID = pm1.post_id)
INNER JOIN wp_postmeta As pm2 ON (p.ID = pm2.post_id)
WHERE p.post_type IN ('knl_meals') 
AND p.post_status = 'publish'
AND x.taxonomy = 'knl_meal_types'

AND pm1.meta_key = '_knl_meal_date'
AND pm2.meta_key = '_knl_meal_regular'

AND STR_TO_DATE(pm1.meta_value, '%M %d, %Y') BETWEEN CURRENT_DATE() AND DATE_ADD(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), INTERVAL 5 DAY)
ORDER BY STR_TO_DATE(pm1.meta_value, '%M %d, %Y') ASC,t.term_id ASC, p.menu_order ASC, p.post_date DESC;

Basically, I want meal_plan to be meta_value for meta_key = _knl_meal_regular if it exists, otherwise empty string/bool false etc type value for the post but the post should be included in the results. Right now, it is excluding the posts if meta_key does not exist for a post.

0

Thanks to @Hobo's answer which gave me the clue for moving pm2 condition to the join statement

SELECT p.ID, p.post_content, p.post_title, p.post_excerpt, pm1.meta_value as meal_date, pm2.meta_value as meal_plan, t.name as meal_type
FROM wp_posts AS p 
LEFT JOIN wp_term_relationships AS r ON (p.ID = r.object_id)
INNER JOIN wp_term_taxonomy AS x ON (r.term_taxonomy_id = x.term_taxonomy_id)
INNER JOIN wp_terms AS t ON (r.term_taxonomy_id = t.term_id)
INNER JOIN wp_postmeta As pm1 ON (p.ID = pm1.post_id)
LEFT JOIN wp_postmeta As pm2 ON (p.ID = pm2.post_id and pm2.meta_key='_knl_meal_regular')
WHERE p.post_type IN ('knl_meals') 
AND p.post_status = 'publish'
AND x.taxonomy = 'knl_meal_types'

AND pm1.meta_key = '_knl_meal_date'
AND STR_TO_DATE(pm1.meta_value, '%M %d, %Y') BETWEEN CURRENT_DATE() AND DATE_ADD(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), INTERVAL 5 DAY)
ORDER BY STR_TO_DATE(pm1.meta_value, '%M %d, %Y') ASC,t.term_id ASC, p.menu_order ASC, p.post_date DESC;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.