Returning Max and Min Values of Custom Field for a Query WordPress
- SOLVED
Hello all,
I would like to return the highest and lowest values of a couple custom fields associate with Woocommerce products ( the deposit cost, and full cost of the products) based on user.
So I have the simple query (that is within another query):
new WP_Query( array( 'post_type' => 'products', 'post_status' => 'publish', 'author' => $guideid ) );
For each one of those posts there is a required custom field value of 'wpcf-cost'
So what I am looking for is some code, presumably using the php min() and max() functions that will return the highest and lowest values for wpcf-cost
____
In context:
The parent query is listing each shop owner's page. This query is meant to find the cost range for the products offered in their shop. So if I have a listing for Susie's cookies the listing will say 'Susie's Cookies cost from $3-$20' on her listing. Along with the ranges for the other store owners.
Answers (3)
Francisco Javier Carazo Gil answers:
Hi I would do it directly with WPDB and a SQL query.
Francisco Javier Carazo Gil comments:
Something like this:
$min = $wpdb->get_var('SELECT MIN(meta_value) AS min FROM '.$wpdb->prefix.'postmeta AS pm, " .$wpdb->prefix.'posts AS po WHERE pm.post_id = po.ID AND po.post_status = "publish" AND po.post_type = "products" AND post_author = "' . $id_post_author . '"');
Kyle comments:
I will try this and come back thanks
Kyle comments:
Do I need to define the meta key in there somewhere?
Kyle comments:
This isn't working for me at all, could there be an type in there somewhere. I'm not great with sql so I can't proofread it well
Francisco Javier Carazo Gil comments:
Sorry, yes you have to define it:
$min = $wpdb->get_var('SELECT MIN(meta_value) AS min FROM '.$wpdb->prefix.'postmeta AS pm, " .$wpdb->prefix.'posts AS po WHERE pm.post_id = po.ID AND po.post_status = "publish" AND po.post_type = "products" AND po.post_author = "' . $id_post_author . '" AND pm.meta_key = "wpcf-cost"');
Try now please.
Kyle comments:
I am still getting an error when I do this
Dbranes answers:
Hi, you could try the <strong>WP_Query</strong> with <strong>orderby</strong> as <em>meta_value_num</em> and use <em>order</em> with DESC to get the max, and ASC to get the min, like this:
$max_query = new WP_Query( array( 'post_type' => 'products', 'post_status' => 'publish', 'author' => $guideid,
'orderby'=>'meta_value_num', 'order'=>'DESC','meta_key'=>'wpcf-cost','posts_per_page'=>1) );
$min_query = new WP_Query( array( 'post_type' => 'products', 'post_status' => 'publish', 'author' => $guideid,
'orderby'=>'meta_value_num', 'order'=>'ASC','meta_key'=>'wpcf-cost','posts_per_page'=>1) );
Kyle comments:
Thanks that did it!
I did not know about 'meta_value_num', that does the job nicely
John Cotton answers:
<blockquote>So what I am looking for is some code, presumably using the php min() and max()</blockquote>
The problem with that is that the custom field value won't be in the array of returned products (by default at least) and so you've either got to add it or retrieve the values separately.
If you're going to do it separately, you might as well let a SQL query do it for you:
SELECT MAX(meta_value) AS max_price, MIN(meta_value) AS min_price FROM wp_postmeta WHERE meta_key = 'wpcf-cost' AND ...
The only issue is what to add as a filter on the where clause to get just the current shop. Without seeing the rest of your site I can't say whats best, but if you are using a taxonomy you could do a join through the wp_term_relationships to get a list of object ids. Alternatively, you could collect the object ids (the post id) in the loop through the list as it gets output but that would mean a) always displaying all products (do you do that or do you have paging?) and b) buffering that list if you want to max/min to output earlier in the HTML. So, a join through the taxonomies would be better...
Kyle comments:
Thanks for the reply
I am not looking to return the products at all on this page so that's no problem. The idea is something like when you go on urbanspoon and next to the restaurant it might say $, $$, $$$, $$$$, $$$$$ to give a feel for the general price range of that restaurant. So I wanted to give a product price range for each shop.
I will try the code and see how it goes