When it comes to searching the WordPress database, there’s probably no better API than WP_Query. That doesn’t mean it’s not without it’s limitations, though (only which will be improved over time, I’m sure).
For example, consider the case where you have two types of meta data that are completely unrelated and you need to run a search for both of them. Essentially, you have WP_Query and multiple meta keys that you need in order to search for information from this database.
In this specific case, I’m not talking about a single piece of meta data that has multiple keys and that would support and AND or and OR clause.
Instead, I’m talking about the idea of having multiple meta keys each of which are associated with a given post, but must all be searched in order to find the posts that have the data associated with it.
In more technical terms, I’m looking for the intersection of post IDs all of which share a common set of multiple, unrelated meta keys.
Confused? Me too. This is why it helps to draw stuff out in a notebook whenever you’re working on something, but I digress.
WP_Query notes as provided by my two year old
Instead, perhaps code is the better way to do.
WP_Query and Multiple Meta Keys
Let’s say that we have the following two meta keys (to keep it simple):
- The type of account (be it public or private, for example) that is specified in the `$_POST` submission of the page.
- Minimum and maximum values for which a given post can have associated with, say, its cost.
As far as I know (and more on this in a minute), we need to run two instances of WP_Query and then collect the post IDs into an array. From there, we’ll then find the intersection of the post IDs that match the following criteria.
With that said, here’s the first example of looking at the type of account:
In the above example, we’re pulling back all of the posts that have public or private selected somewhere in their meta data (even if it’s in a custom meta box). After that, we’re storing the results in an array and we’re making sure to filter our any duplicates that may exist. This last step is really just a safe guard.
Next, let’s say that we’re looking at another set of meta data, say, the minimum or maximum values that a post has associated with it in terms of cost. Furthermore, we want to make sure that all records retrieve fit between a set amount of data.
To keep things simple, we’ll assume all values are stored as numeric values (rather than, say, $1,000,000 we’ll just use 1000000).
Okay, so at this point we need yet another instance of WP_Query in order to search the posts, so we’ll set it up to do something like this:
Once you’ve reached this point, you should have two arrays. Each of the arrays are going to contain their own sets of posts IDs but there will be some common IDs to each array – that’s what we’re after.
Luckily, PHP makes it really easy to find the intersection of two arrays:
At this point, you have the results that match the two criteria above.
Two important things to note about this is that this can be done effectively using raw SQL and it may even be more efficient; however, I try to stick with the API whenever possible. Since that level is abstraction is provided for a reason, I try to stick with that reason.
Secondly, I’m not above saying that I may have missed something completely and this is not the right way to do this. If that’s the case, I’m open to any and all suggestions and am willing to mention it in an update to the post by linked to the proper comments.
Whatever the case, this is something that does work and is relatively straightforward in how to achieve it using WP_Query until a more elegant solution comes along.
Forgive me if I missed something but is the i after the wp_reset_postdata(); in the second query just a typo?
Also, you said it could be done more effectively using raw SQL. Can you explain that? I figured the way you have it written now would be the most effective since it uses what WP already has built.
Just a typo :). All fixed now.
I didn’t say it could be written more effectively, I just should that it could be written in SQL and it may be more efficient to do so.
I’m not a database administrator and SQL is not my strong suit, but rather than having several queries setup and then iterating through them, my guess is that you’d be able to set up an SQL statement that’s a little more complicated that can retrieve everything at once rather than doing it the way I’ve shared.
Thanks Tom, this will come in handy when creating more complex database queries while sticking to the WP_Query API.
Very good, thanks for sharing.
You got it, Dylan.
Awesome, very clever! One question, the WordPress API doesn’t allow to do what you suggested in your article. Do you have a suggestion of how it could be done in case it existed?
Hey Renato – not sure what you mean. This is using the WP API as I’ve used it in a recent project.
That’s a pretty good solution using array_intersect to compare the values.
When I encountered this problem in a past project i created an array of result post ID’s from the first query and used them as criteria for the second query.
I’m no performance specialist so I’d be interested to see how the two solutions compare. I suspect my second WP_Query is probably heavier but I don’t need to run any functions like intersect on the server afterwards.
Performance is one of those things that I focus on depending on the nature of the data set I’m working with. That is, if the data that I’m retrieving and having to, say, determine the intersection, then it’s not such a big deal.
But when you’re working with larger data sets, things like this may not work and it may require something a little bit different. In that case, I try something different :).
Thanks for explaining this in a clear and concise way. Three years ago I was working on a project where knowing how this (to extract the common ID from 2 separate queries) would have been extremely useful, but as the saying goes: better late than never :)
Glad to have been of some help, Piet!
Thanks a lot, I haven’t tried this yet but it is the solution I’ve been looking for. If it is too slow I will write the SQL. I honestly can’t believe that we have to go through this though, it seems like a case that would pop up often. I’m developing a widget for a client that searches their posts and we have check-boxes for area. So my first technique after finding this shortcoming in the API was to query the unique values from $wpdb->postmeta matching the meta key in question using ‘SELECT DISTINCT’, then I made an array of $non_intersecting_values between the array of $complete_values and array of $searched_values and used that to create a ‘meta_query’ that uses only ‘NOT LIKE’. That worked well, but now I have to do more cases like this and I’m afraid it could get to be taxing on the page. I’m going to try your way now, thanks.
Also, if you don’t mind me answer one of the questions in your comments. Using WP_Query will become slower then pure SQL because WP has to parse the $args passed to WP_Query and build it’s own SQL query. The benefit is that WP attempts to make queries work across different SQL versions and setups effectively, and the WP developers have a lot of experience building SQL Queries in WP. Using the $wpdb object is the best way to make your own custom queries using SQL in WordPress as you get the benefit of SQL and WP integration. However for most cases the WP_Query is more then perfect.
Hey Tom,
An update regarding this article. I’ve just noticed this in the WP_Query documentation regarding WP 4.1
This should make it loads easier to do these complex queries!
“Starting with version 4.1, meta_query clauses can be nested in order to construct complex queries. For example, “show me products where color=orange OR color=red&size=small” translates to the following:
$args = array(
‘post_type’ => ‘product’,
‘meta_query’ => array(
‘relation’ => ‘OR’,
array(
‘key’ => ‘color’,
‘value’ => ‘orange’,
‘compare’ => ‘=’,
),
array(
‘relation’ => ‘AND’,
array(
‘key’ => ‘color’,
‘value’ => ‘red’,
‘compare’ => ‘=’,
),
array(
‘key’ => ‘size’,
‘value’ => ‘small’,
‘compare’ => ‘=’,
),
),
),
);
$query = new WP_Query( $args );”
http://codex.wordpress.org/Class_Reference/WP_Query#Custom_Field_Parameters
Thanks for keeping this current, Grant!
that photo of the flow chart as created by your 2-year old made my day.
Made mine, too ;)
Hey Tom, thanks for this, it’s exactly what I’ve been looking for!
By any chance would you know how to ignore a custom field in the query if the value is empty.
For example:-
$args = array(
‘post_type’ => ‘acme’,
‘meta_query’ => array(
‘relation’ => ‘AND’,
array(
‘key’ => ‘key1’,
‘value’ => $value,
),
array(
‘key’ => ‘key2’,
‘value’ => $value,
)
If key2 was empty then I’d want it to ignore that key and continue with the query.
As it is, if key2 was empty then it wouldn’t find any posts.
Sorry if that doesn’t make any sense!
I think I get what you’re asking: Basically, you want to setup a query but if the second value is empty, you don’t want to pass it as a parameter, right?
If that’s the case, I think it depends on a number of different things:
Without knowing more, I’d first check to see if the second key is empty. If so, then change arguments for the query.
There are times when writing direct database queries are the best route to go. I always prefer the API over that, but if you’re in a situation where you’ve got a variable number of arguments, sometimes you may have to go for something different.
That said, nothing you’ve laid out says you need to go that route, yet :). I hope that helps!