Improving multi-and query

Question

SELECT wposts.*, wpostmeta.* 
FROM wp_posts wposts, wp_postmeta wpostmeta, wp_postmeta wpostmeta2, wp_postmeta wpostmeta3 
WHERE 
      wposts.ID = wpostmeta.post_id 
  AND wposts.ID = wpostmeta2.post_id 
  AND wposts.ID = wpostmeta3.post_id 
  AND wpostmeta.meta_key = 'listing_subtype' 
  AND wpostmeta.meta_value = 'Seattle' 
  AND wpostmeta2.meta_key = 'district' 
  AND wpostmeta2.meta_value = 'Breadview' 
  AND wpostmeta3.meta_key = 'price_current' 
  AND wpostmeta3.meta_value BETWEEN 0 AND 800000
  AND wposts.post_status = 'publish' 
  AND wposts.post_type = 'vreb_property' 
ORDER BY wposts.post_date DESC 
LIMIT 0, 20

I'm looking at this query now and thinking that I should probably improve upon it's efficiency. And I've been experimenting with so many variations, that I think it would be best to ask for SO input.

Answer 1

You want to do this with a group by and having clause. To get the lists of posts:

select p.id
from wp_post_meta p
group by p.id
having sum(case when p.meta_key = 'listing_subtype' and p.meta_value = 'Seattle' 
                then 1 else 0 end) > 0 and
       sum(case when p.meta_key = 'district' and p.meta_value = 'Breadview'
                then 1 else 0 end) > 0 and
       sum(case when p.meta_key = 'price_current' and cast(p.meta_value as float) BETWEEN 0 AND 800000
                then 1 else 0 end) > 0

Note: This is untested so it might have a syntax error.

To get all information, join back the posts:

select p.*
from wp_posts p join
     (select p.id
      from wp_post_meta p
      group by p.id
      having sum(case when p.meta_key = 'listing_subtype' and p.meta_value = 'Seattle' 
                      then 1 else 0 end) > 0 and
             sum(case when p.meta_key = 'district' and p.meta_value = 'Breadview'
                      then 1 else 0 end) > 0 and
             sum(case when p.meta_key = 'price_current' and cast(p.meta_value as float) BETWEEN 0 AND 800000
                      then 1 else 0 end) > 0
    ) p2
    on p.id = p2.id