SQL WordPress Query – inner vs outer join

1 comment

1. That’s a pretty intense query. It’s hard to know exactly how to do this efficiently without knowing your table structures better (seems like a few of those JOINS could be combined).

   Here is a stab just by editing your original query:

   SELECT 
       p.*,
       pm.*,
       pm_featured.meta_value AS featured,
       pm_latlng.meta_value AS latlng
   
   FROM 
       wp_posts p
       JOIN wp_postmeta pm
         ON pm.post_id = p.ID
       JOIN wp_postmeta pm_propdetails
         ON (pm_propdetails.post_id = p.ID AND pm_propdetails.meta_key = 'propdetails')
       LEFT JOIN wp_postmeta pm_featured
         ON (pm_featured.post_id = p.ID AND pm_featured.meta_key = 'featured-property')
       LEFT JOIN wp_postmeta pm_latlng
         ON (pm_latlng.post_id = p.ID AND pm_latlng.meta_key = 'martygeocoderlatlng')
   
       JOIN wp_term_relationships tr
         ON tr.object_id = p.ID
       JOIN wp_term_relationships tr_taxrel
         ON (tr_taxrel.object_id = p.ID AND tr_taxrel.term_taxonomy_id = 12)
   
   WHERE
       (pm_propdetails.meta_value LIKE '%Vacant Unrented Ready%'
           OR
        (pm_featured.meta_value = 'on' OR pm_feature.meta_value IS NULL))
   
       AND p.post_type = 'property'
       AND p.post_status = 'publish'
   
   GROUP BY p.ID
   ORDER BY p.post_date DESC
   

   It’s just using LEFT JOINs to try to return the posts where meta_key might be missing, and then the WHERE section was modified to try to account for if pm_featured.meta_value IS NULL.

   Could try another version if you provide some more info.

   Edit:
   According to your question, this might also work. Really just a huge shot in the dark though =)

   SELECT T2.*, T1.featured, T1.latlng FROM
   (
       SELECT post_id, SUM(IF((meta_key='propdetails' AND meta_value LIKE '%Vacant Unrented Ready%') OR ((meta_key='featured' AND meta_value='on')),1,0)) AS Keeper
       , MAX(IF(meta_key = 'featured-property',meta_value,NULL)) AS featured
       , MAX(IF(meta_key = 'martygeocoderlatlng',meta_value,NULL)) AS latlng
       FROM wp_postmeta 
       GROUP BY post_id
       HAVING Keeper>0
   ) AS T1
   JOIN
   wp_posts AS T2
   ON T1.post_id=T2.ID
   AND T2.post_type = 'property'
   AND T2.post_status = 'publish'
   JOIN
   wp_term_relationships AS T3
   ON T2.ID=T3.object_id AND T3.term_taxonomy_id = 12
   GROUP BY T2.ID
   ORDER BY T2.post_date DESC
   

   Since the second query ended up working (A little surprising, I made a lot of assumptions 😉 this one should be slightly faster and a little easier to understand…
   (Note: especially if you have a composite index on (meta_key,meta_value) in the wp_postmeta table.)

   SELECT T2.*, T1.featured, T1.latlng FROM
   (
       SELECT postmetas_info.post_id
       , MAX(IF(meta_key = 'featured-property',meta_value,NULL)) AS featured
       , MAX(IF(meta_key = 'martygeocoderlatlng',meta_value,NULL)) AS latlng
       FROM 
       (
           SELECT DISTINCT post_id FROM wp_postmeta WHERE (meta_key='propdetails' AND meta_value LIKE '%Vacant Unrented Ready%') OR (meta_key='featured' AND meta_value='on')
       ) AS postmetas_applicable
       JOIN
       wp_postmeta AS postmetas_info
       ON postmetas_applicable.post_id=postmetas_info.post_id
       GROUP BY postmetas_info.post_id
   ) AS T1
   JOIN
   wp_posts AS T2
   ON T1.post_id=T2.ID
   AND T2.post_type = 'property'
   AND T2.post_status = 'publish'
   JOIN
   wp_term_relationships AS T3
   ON T2.ID=T3.object_id AND T3.term_taxonomy_id = 12
   GROUP BY T2.ID
   ORDER BY T2.post_date DESC
   

   The SUM(IF()) in the previous query is kind of fun but probably not as performant as it could be since it evaluates every row in the table. The query above whittles down the post_id’s first and then just grabs the corresponding data based on only eligible post_id’s after that.

   Log in to Reply