I noticed that there are bunch of operator can be use for compare in meta_query.
However, I am not quite sure what operator I should use, it is somehow confusing like
I would like to know what exactly each operator mean, and in what condition I should use them.
= != > >= < <= LIKE NOT LIKE IN NOT IN BETWEEN NOT BETWEEN NOT EXISTS
The first several work as you would expect:
= equals != does not equal > greater than >= greater than or equal to < less than <= less than or equal to
LIKE and NOT LIKE
NOT LIKE are SQL operators that let you add in wild-card symbols, so you could have a meta query that looks like this:
array( 'key' => 'name', 'value' => 'Pat', 'compare' => 'LIKE' )
This would return all posts where the meta value “name” has the string “Pat”. In this case, “Pat” “Patricia” and “Patrick” would all be returned back to you. There’s a non-WordPress tutorial explanation here.
Adding the wildcard character
% isn’t necessary, because it gets added by default like @Herb said in his below answer. Like this:
$meta_value="%" . like_escape( $meta_value ) . '%'; – see source.
IN and NOT IN
NOT IN select any matches that are in (or not in) the given array. So you could do something like this:
array( 'key' => 'color', 'value' => array('red', 'green', 'blue') 'compare' => 'IN' )
and it would get all posts that have the color set to either red, green, or blue. Using ‘NOT IN’ gets the reverse, any posts that have a value set to anything else than what’s in the array.
The generated SQL for this would look something like this:
SELECT * FROM posts_meta WHERE value IN ("red", "green", "blue")
BETWEEN and NOT BETWEEN
NOT BETWEEN allow you to define a range of values that could be correct, and require you to give two values in an array in your meta_query:
array( 'key' => 'price', 'value' => array(20,30) 'compare' => 'BETWEEN' )
This will get you all posts where the price is between 20 and 30. This person digs into an example with dates.
NOT EXISTS is just like what it sounds – the meta value isn’t set or is set to a null value. All you need for that query is the key and comparison operator:
array( 'key' => 'price', 'compare' => 'NOT EXISTS' )
This person needed to query non-existent meta values, and needed them to play nice with others.
Hope this helps!