lpangm03 lpangm03 - 5 months ago 10
SQL Question

How would I build this query, if it's possible?

So I am facing a challenge on a WordPress project I am working on, and I am hoping that someone can give me a tip on the best way to accomplish this. So I am using Advanced Custom Fields on a custom post type, and I have about 10 different fields made. I am having to build a search that needs to match the value in the advanced custom fields and return the post id.

The custom fields stores it's data in the wp_postmeta table and looks something like this:

+------------------------------------------+
|meta_id | post_id | meta_key | meta_value |
|------------------------------------------|
| 1 | 24 | state | CA |
| 2 | 24 | city | Yuba City |
| 3 | 24 | type | Full time |
+------------------------------------------+


(with a lot more, but keeping it short).

So if my search fields are "State", "City", and "Job Type" and all three are filled in, what I need to happen is to return the post_id if all three meta values match what was searched for.

I've never had to do something like this where they are in multiple rows, so I'm not entirely sure how I would go about this.

Answer

One method uses aggregation. One method uses join. For instance:

select ps.post_id
from wp_postmeta ps join
     wp_postmeta pc
     on ps.post_id = pc.post_id and
        ps.meta_kay = 'state' and ps.meta_value = 'CA' and
        pc.meta_key = 'city' and pc.meta_value = 'Yuba City' join
     wp_postmeta pt
     on pt.post_id = ps.post_id and
        pt.meta_key = 'type' and pt.meta_value = 'Full time';
Comments