wordpress postmeta custom query issues

Question

I am trying to build a custom query that will check specific postmeta fields to see. I am having an issue with too many rows returned when I am not using a "AND (b.meta_key = 'x' AND b.meta_value = 'y')".

At times, I will be using a "b"... but not always.

In the second query below, I am getting 18 rows returned(one for every meta field filled out + 1 more for a). How can this be corrected so that I can have multiple postmeta matches and not have all of the rows returned.

This Query returns 1 post

SELECT wp_posts.* 
FROM 
    wp_posts, 
    wp_postmeta a
WHERE 
    wp_posts.ID = a.post_id 

    AND (
    a.meta_key = 'property_space_requirement'
    AND a.meta_value = '1000' 
    )

AND wp_posts.post_status = 'publish' 
AND wp_posts.post_date < NOW()
ORDER BY wp_posts.post_date DESC

THis QUery returns 18 posts - I want this to ONLY return the 1 from above if I am not using a "AND b"

SELECT wp_posts.* 
FROM 
    wp_posts, 
    wp_postmeta a,
    wp_postmeta b
WHERE 
    wp_posts.ID = a.post_id 
    AND wp_posts.ID = b.post_id

    AND (
    a.meta_key = 'property_space_requirement'
    AND a.meta_value = '1000' 
    )

AND wp_posts.post_status = 'publish' 
AND wp_posts.post_date < NOW()
ORDER BY wp_posts.post_date DESC

This Query returns 1 post

SELECT wp_posts.* 
    FROM 
        wp_posts, 
        wp_postmeta a,
        wp_postmeta b
    WHERE 
        wp_posts.ID = a.post_id 
        AND wp_posts.ID = b.post_id

        AND (
        a.meta_key = 'property_space_requirement'
        AND a.meta_value = '1000' 
        )

AND (
        b.meta_key = 'xx'
        AND b.meta_value = 'yy' 
        )

    AND wp_posts.post_status = 'publish' 
    AND wp_posts.post_date < NOW()
    ORDER BY wp_posts.post_date DESC

Answer 1

Dealing with postmeta can be a pain in the neck; you basically need to create a virtual table for each key value you want to look up.

You seem to want posts which have property_space_requirement = 1000 and xx = yy, as well as some post criteria.

Here's your virtual table for postmeta.

 SELECT post_id AS ID,
        meta_value AS property_space_requirement
   FROM wp_postmeta
  WHERE meta_key  = 'property_space_requirement'

Ditto for xx/yy

 SELECT post_id AS ID,
        meta_value AS xx
   FROM wp_postmeta
  WHERE meta_key  = 'xx'

You then need to LEFT JOIN these virtual tables to the posts table, like so

SELECT p.*
  FROM wp_posts AS p
  LEFT JOIN (
               SELECT post_id AS ID,
                      meta_value AS property_space_requirement
                 FROM wp_postmeta
                WHERE meta_key  = 'property_space_requirement'
            ) AS a ON p.ID = a.ID
  LEFT JOIN (
               SELECT post_id AS ID,
                      meta_value AS xx
                 FROM wp_postmeta
                WHERE meta_key  = 'xx'
            ) AS b ON p.ID = b.ID
 WHERE p.post_status = 'publish'
   AND p.post_date < NOW()
   AND a.property_space_requirement = 1000
   AND b.xx = 'yy'
 ORDER BY p.post_date DESC

The trick here is to use the subquery to transform one key's value of your postmeta into a column in your result set, then use a WHERE clause to filter on it.

Notice that you can use all sorts of more elaborate WHERE filters with this setup. For example, you could do a more elaborate range search, something like this.

 WHERE ...
   AND (  (a.property_space_requirement <= 1000 AND b.xx = 'yy')
        OR(a.property_space_requirement > 1000 AND b.xx = 'zz') )
 ORDER BY a.property_space_requirement DESC

You can add as many key/value criteria to your query as you wish by LEFT JOINing new virtual tables like the two already shown.

Beware: the WordPress core team just announced that they are going to retire the old mysql_ interface in the next year. Look up how to use WordPress's internal wpdb class.