Wordpress - SQL query on two custom fields issue

Question

I've got this query so far:

if(!empty($_SESSION['s_property_region'])) {
    $sqlWHERE .= " AND $wpdb->postmeta.meta_key = 'property_region' AND $wpdb->postmeta.meta_value = '".$_SESSION['s_property_region']."'";
}
if(!empty($_SESSION['s_property_bedrooms'])) {
    $sqlWHERE .= " AND $wpdb->postmeta.meta_key = 'property_bedrooms' AND $wpdb->postmeta.meta_value = '".$_SESSION['s_property_bedrooms']."'";
}
$sql    =   "SELECT $wpdb->posts.ID
             FROM $wpdb->posts
             LEFT JOIN $wpdb->postmeta ON ($wpdb->posts.ID = $wpdb->postmeta.post_id)
             WHERE $wpdb->posts.post_status = 'publish'
             AND $wpdb->posts.post_type = 'property'
             ".$sqlWHERE."
             ORDER BY $wpdb->posts.post_date DESC";

It works if I just search for one custom field but not the two together. I know why and its because the way the LEFT Join works. There isnt going to be a row where post_type can be two things at once.

after the join Im going to get a structure like this:

|   ID   |   meta_key          |   meta_value   |
|   1    |   property_region   |   East         |
|   1    |   property_bedrooms |   4            |
|   2    |   property_region   |   West         |
|   2    |   property_bedrooms |   2            |

is there a query that can turn the two tables into a structure like this:

|   ID   |   property_region   |   property_bedrooms   |
|   1    |   East              |   4                   |
|   2    |   West              |   2                   |

If the query can make the table look like above then my simple WHERE this=that AND this=that will work.

Any help on this matter is greatly appreciated

Thanks

Scott

EDIT: After some searching about I now put this code together: http://pastebin.com/5YYDLyeR It works and returns the structure I'm after but as soon as I try to do a WHERE on property_region and property_bedrooms I get: #1054 - Unknown column 'property_bedrooms' in 'where clause'

EDIT2: Ok I have taken PMV code and to be able to do my WHERE clause I've had to do a sub-query: http://pastebin.com/dvTgsU7S This code does what I want but are there any better ways of achieving this query? I presume performance will be an issue when I have 1000+ entries in the DB...

Answer 1

Feed your entire query into something like this:

SELECT ID
,      MAX(CASE WHEN meta_key = 'property_region' THEN meta_value END) AS property_region
,      MAX(CASE WHEN meta_key = 'property_bedrooms' THEN meta_value END) AS property_bedrooms
FROM (your query)
GROUP BY ID
HAVING MAX(CASE WHEN meta_key = 'property_region' THEN meta_value END) = 'east'
AND    MAX(CASE WHEN meta_key = 'property_bedrooms' THEN meta_value END) = 4

EDIT: The HAVING clause. It's ugly, but I think it's what you're looking for. You'll still have to build it in the php code, but I think it should give you what you want.