Multiple Meta Query Not Working WordPress
- SOLVED
I'm using Custom Meta Boxes and Fields to set meta values for a custom post type called events. One of which is a "linked" custom post type (called a program) that is set via a multi-check field. My test is on posts that only have one value checked, the resulting meta value is a number (post id for the program post type).
On the events archive template I'm setting the query and trying to have the ability add a query variable that will update the meta query based on the linked program field (field id is event_program). I've correctly added the query variable so it will work, but the resulting wp_query doesn't show posts (even though there are posts with all the required meta query parameters).
Here is the modified query code that I'm using (it's wrapped in a class, hence the $this mentions):
// Modify Event Listings query
add_action( 'pre_get_posts', array( $this, 'event_query' ) );
/**
* Modify WordPress query where needed for event listings
*
* @param object $query
*/
function event_query( $query ) {
// If you don't want the plugin to mess with the query, use this filter to override it
$override = apply_filters( 'events_manager_query_override', false );
if ( $override )
return;
if ( $query->is_main_query() && ( is_post_type_archive( 'events' ) || is_tax( 'event-category' ) || is_post_type_archive( 'recurring-events' ) ) ) {
// Front end
if( ! is_admin() ) {
$meta_query = array();
// if the query variable is for past events, show past events
if( get_query_var( 'event-type' ) == 'past' ) {
$meta_query[] = array(
'key' => 'event_end',
'value' => time(),
'compare' => '<',
);
$query->set( 'order', 'DESC' );
// Otherwise show upcoming events
} else {
$meta_query[] = array(
'key' => 'event_end',
'value' => time(),
'compare' => '>',
);
$query->set( 'order', 'ASC' );
}
// If there is a program query
if( $program = get_query_var( 'event_program' ) ) {
$meta_query[] = array(
'key' => 'event_program',
'value' => $program
);
}
$meta_query[] = array(
'key' => '_thumbnail_id'
);
$query->set( 'meta_query', $meta_query );
$query->set( 'posts_per_page', 7 );
$query->set( 'orderby', 'meta_value_num' );
$query->set( 'meta_key', 'event_start' );
// Admin side
} else {
$meta_query = array(
'relation' => 'OR',
array(
'key' => 'event_start',
),
array(
'key' => 'event_start',
'compare' => 'NOT EXISTS',
'value' => '',
),
array(
'key' => 'event_start',
)
);
$query->set( 'meta_query', $meta_query );
$query->set( 'meta_key', 'event_start' );
$query->set( 'orderby', 'meta_value' );
$query->set( 'order', 'ASC' );
}
}
}
the above code works for everything else, but whenever the url query variable 'event_program' is set it results in no posts found. I can print out the wp_query and see that the meta query is set up correctly, and that the query variable is working. I don't know why it's not giving me any results...
The key part is this, and it isn't working:
// If there is a program query
if( $program = get_query_var( 'event_program' ) ) {
$meta_query[] = array(
'key' => 'event_program',
'value' => $program
);
}
Answers (3)
John Cotton answers:
Try this:
if( $program = get_query_var( 'event_program' ) ) {
$meta_query[] = array(
'key' => 'event_program',
'value' => $program,
'compare' => 'LIKE'
);
}
Joshua Nelson comments:
John,
The issue with 'LIKE' is that it will return any post with a value for the meta field, not the posts with a specific value.
Thanks,
Joshua
John Cotton comments:
<blockquote>The issue with 'LIKE' is that it will return any post with a value for the meta field, not the posts with a specific value</blockquote>
If you've got serialized values, that's the most practical option.
What is the range of possible values? It might that you can add some refinement in the query....
Joshua Nelson comments:
The value is an array of post ids, it has to match the correct post id - not a range. Each "event" can be optionally linked to another cpt, "program" - I need to be able to filter the archive to show events linked to a specific program. I may have to refine this to a dropdown, but I think the client preferred the ability to have a single event potentially linked to multiple programs.
John Cotton comments:
<blockquote>( array( [0] => 81 )</blockquote>
So for that value, your LIKE would be
'value' => "i:0;i:$program;",
The delimiters will stop any non-positive matches.
Of course, it relies on knowing the array index but perhaps it's always 0?
John Cotton comments:
<blockquote>The value is an array of post ids, it has to match the correct post id - not a range</blockquote>
Sure, I understood that. I meant, what might possible values of the array be (eg "always a single integer", or "always two numbers").
Joshua Nelson comments:
Ah, ok, cool to know! Unfortunately, it won't necessarily be 0 every time. Why does 'IN' not work to select the value from an array? The codex would imply that this is the correct method. Do I need to change the value in some way?
Joshua Nelson comments:
Your second question - it's always a post id. Array( [0] => 81, [1] => 324 ) et cetera
John Cotton comments:
<blockquote>Why does 'IN' not work to select the value from an array?</blockquote>
Because it's not an array in the database, it's a string.
IN only works where you have single, non-serialized values in the meta_value field.
Joshua Nelson comments:
OK, just want to make sure I'm on the same page. The post's meta value will always be an array. The meta query could be a string or an array, just depending on how I manipulate the value. What you're saying is, because the post's meta value is an array, it's causing the issue. I could use an array of potential values and then use 'IN' to determine if the post meta value was in that array, but not that other way around?
If so, I'll need to either change the field or alter my approach to filtering by this meta field.
John Cotton comments:
If you're stuck with serialized values then - if it were me - I'd use LIKE to restrict the list:
'value' => "i:$program;",
And then double check in the output loop for false positives, ie that I hadn't picked up a record where is was the index of the array that matched.
eg
"i:87;i:4;"
where 87 is the array index and 4 is the post id value.
John Cotton comments:
<blockquote> What you're saying is, because the post's meta value is an array, it's causing the issue. I could use an array of potential values and then use 'IN' to determine if the post meta value was in that array, but not that other way around?</blockquote>
What I'm saying is that the <em>PHP array</em> is stored as a <em>string</em> in the database*. So you can't use normal query rules.
So either change the way you are storing it (if that's possible) or do the hack in your query/loop as I suggest above.
* To be clear, all meta values are stored as strings, it's just that SQL doesn't know about serialized PHP arrays.
Joshua Nelson comments:
John, great - that's how I understood it, didn't realize initially that it would cause an issue. Thanks for the clarification. Unfortunately, the hack you offered doesn't work on my end - but that's fine, I'm going to explore either modifying the main loop and/or template to achieve this filter after the query has run. Thanks!
Arnav Joy answers:
I think the values are saved in serialized form and that's why this query is not working ..
check these two links and see if that helps:-
http://wordpress.stackexchange.com/questions/16709/meta-query-with-meta-values-as-serialize-arrays
http://stackoverflow.com/questions/18036471/wordpress-meta-query-arrays
Joshua Nelson comments:
[[LINK href="https://github.com/WebDevStudios/Custom-Metaboxes-and-Fields-for-WordPress/wiki/Field-Types#multicheck"]]The meta values[[/LINK]] are stored in an array! Specically, something like this: array( [0] => 81 )
The value I am putting into the meta query is a string. When I change it to an array it still doesn't work. The same goes if I change the compare to 'IN'... Even though I can confirm that a post with that meta value exists.
The url query variable set to: /events/?event_program=81 results in the following wp_query output for the meta query. The only added part is the event_program portion, the rest is the standard meta query for the archive that returns results.
[meta_query] => WP_Meta_Query Object
(
[queries] => Array
(
[0] => Array
(
[key] => event_start
)
[1] => Array
(
[key] => event_end
[value] => 1418064491
[compare] => >
)
[2] => Array
(
[key] => event_program
[value] => Array
(
[0] => 81
)
[compare] => LIKE
)
[3] => Array
(
[key] => _thumbnail_id
)
)
[relation] => AND
)
Joshua Nelson comments:
Also, a follow up. Trying a string for the value and compare to 'IN' doesn't work either. I've tried it as an array with 'IN' and a string with 'IN' and both without setting a compare parameter. According to the codex, if the value is an array it needs to be 'IN', 'BETWEEN', et cetera.
Dbranes answers:
How is the meta data saved in your database?
Do you have an example?
Is it serialized as @ArnavJoy suggests?
Joshua Nelson comments:
Dbranes,
thanks! You can see responses to your questions in my response above @ArnavJoy - the array bit was something, but it didn't result in any fix.
Thanks,
Joshua
Dbranes comments:
Hi Joshua
It can be problematic dealing with serialized arrays, as described by @John.
You might also want to consider adding your post meta with $unique = false, to avoid serialized arrays.
Another option could be to use a custom taxonomy instead of post meta, if that's possible.
Joshua Nelson comments:
Well, there's no way around the serialized array at the moment, that's how Custom Metaboxes and Fields stores multi-check (rightly so). I will modify the loop/template to achieve what I'm looking for and I can use the 'LIKE' to at least narrow-down the results a bit. Thanks!