query_posts ->where meta value is smaller or greater or equals... WordPress
- REFUNDED
I am using query_posts( $args ) to filter the Loop.
I want to filter posts based on their meta_value vote, sometimes smaller then, sometimes equals and so on....
I definitly want to use the query_posts( $args ) function and pass my filter through $args!
I although dont want to use add_filter('posts_where', 'filter_where'); and then add an AND ... String to the query
I want to use the given functionalitie of wordpress t filter post by meta_key, meta_value and meta_compare like this
$args = array( 'meta_key'=>'vote', 'meta_compare'=>'>=', 'meta_value'=>5, 'posts_per_page'=>100 ) )
query_posts( $args );
The result of this is:
SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND wp_postmeta.meta_key = 'vote' AND wp_postmeta.meta_value >= '5' GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 100
The problem of that is:
wp_postmeta.meta_value >= '5' it should be wp_postmeta.meta_value >= 5
then i would work fine.
I dont't get the reason why wordpress adds ''
I'm using a predefined parameter/function from wordpress <, >, <=, >= and its obvious that this will only work with numbers and not strings which would need ''
even the documentation says:
Returns posts with custom field key of 'miles' with a custom field value that is LESS THAN OR EQUAL TO 22
query_posts('meta_key=miles&meta_compare=<=&meta_value=22');
Answers (5)
Pippin Williamson answers:
Try changing
$args = array( 'meta_key'=>'vote', 'meta_compare'=>'>=', 'meta_value'=>5, 'posts_per_page'=>100 ) )
query_posts( $args );
to
query_posts('meta_key=vote&meta_compare=>=&meta_value=5&posts_per_page=100');
chris_ comments:
Your code woul still end in
SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND wp_postmeta.meta_key = 'vote' AND wp_postmeta.meta_value >= '5' GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 100
wp_postmeta.meta_value >= '5' :(
David Navarrete answers:
global $wpdb;
$sql = "
SELECT SQL_CALC_FOUND_ROWS $wpdb->posts.* FROM $wpdb->posts JOIN $wpdb->postmeta ON ($wpdb->posts.ID = $wpdb->postmeta.post_id) WHERE 1=1 AND $wpdb->posts.post_type = 'post' AND ($wpdb->posts.post_status = 'publish' OR $wpdb->posts.post_status = 'private') AND $wpdb->postmeta.meta_key = 'vote' AND $wpdb->postmeta.meta_value >= 5 GROUP BY $wpdb->posts.ID ORDER BY $wpdb->posts.post_date DESC LIMIT 0, 100"
$resultado = $wpdb->get_results($sql, OBJECT);
done!
chris_ comments:
Thats too easy!
I wanted to use query_posts() like i said above i could do that query by my own...
Your solution could even be done more nicely with
add_filter('posts_where', 'filter_where');
and http://codex.wordpress.org/Function_Reference/query_posts
says its possible ..... Returns posts with custom field key of 'miles' with a custom field value that is LESS THAN OR EQUAL TO 22 ....
Kailey Lampert answers:
From what I can tell, using query_posts() means it will compare the values as strings, not as numbers. You can work around this by adding leading zeros - not elegant, but it works.
If you're echoing out that number and you want to remove the leading zeros, you can use the number_format() function to clean it up.
chris_ comments:
You are right but <, >, <= and >= does not compare strings it compares numbers and thats what it should do - thats why its although implemented in wordpress...
I tried your solution and it generated even stranger results converts 011 into '9' and other strange things....
take a look http://codex.wordpress.org/Function_Reference/query_posts it says :"Returns posts with custom field key of 'miles' with a custom field value that is LESS THAN OR EQUAL TO 22" and thats all i want...
Kailey Lampert comments:
Although the documentation says that you can compare values with the < and > symbols, it still compares numbers as strings. This means that 99 would be considered greater than 100 ( 9 > 1 ). ("Note the value 99 will be considered greater than 100 as the data is stored as strings, not numbers")
Logically, you should be able to add leading zeros to fix this (099 would correctly be considered less than 100).
However, if your values are being converted, then unfortunately I am at a loss.
Duncan O'Neill answers:
The problem, as Kailey has pointed out, is that your meta value is considered a string.
I found a couple of pages which deal with the same problem;
Here on wpquestions;
http://wpquestions.com/question/show/id/883
There a filter was used to force the engine to treat the value as an integer.
http://wordpress.org/support/topic/sort-by-custom-field-not-working
In this latter case, 'raw' SQL was used to achieve the outcome.
I've tried using the format you use above, and haven't had any success with forcing the meta value to be treated as an integer.
So it seems you might have to consider one of the above methods.
cheers,
Duncan
Mark Duncan answers:
Have you tried adding 'orderby' => 'meta_value_num' to your args?
Since 3.0.
http://codex.wordpress.org/Version_3.0#Appearance
Not well documented, but it is also mentioned in the orderby section of the query posts codex entry.