One query for all post and all postmeta

Hello,

I’m building an instant search on my site that filters by by post fields and their custom fields. I’m hoping I can gather all that in one query instead of having to query the custom fields in a loop for each post. Is that possible?

I found something like this, that comes close…

$query = "
SELECT *
FROM wp_posts
INNER JOIN wp_postmeta m1
  ON ( wp_posts.ID = m1.post_id )
INNER JOIN {$wpdb->prefix}postmeta m2
  ON ( wp_posts.ID = m2.post_id )
WHERE
wp_posts.post_type = 'post'
AND wp_posts.post_status = 'publish'
AND m1.meta_key = 'ad_city'
AND m2.meta_key = 'ad_state'
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date
DESC;
";

But it has the each of the custom fields with the column name of ‘meta_key’ and one for ‘meta_value’, so I’m not able to pick out the right values for my processing. Seems I could just include an ‘as’ somewhere, but can’t seem to get it to work.

Can someone help? Not attached to the query above, just trying to get all the post and postmeta into one row with a query with specific column names for the custom fields.

Thanks a million!

Philip