Some of my posts (not all) have a price as a meta key/value. Today I use the pre_get_posts action so that my users can search for prices that are between a certain value.
This is the code that I’m using today, and it’s working.
add_action('pre_get_posts', 'my_search_price');
function my_search_price( $query ) {
if ($query->get('maxprice') != "" && $query->get('minprice') != "" && $query->is_main_query()) {
$maxprice = intval($query->get('maxprice'));
$minprice = intval($query->get('minprice'));
$meta = array();
$meta[] =
array (
'key' => 'price',
'value' => $maxprice,
'compare' => '<=',
'type' => 'numeric'
);
$meta[] =
array (
'key' => 'price',
'value' => $minprice,
'compare' => '>=',
'type' => 'numeric'
);
$meta[] =
array (
'key' => 'price_updated',
'value' => time()-(60*60*24*14),
'compare' => '>',
'type' => 'numeric'
);
$query->set('meta_query', $meta);
}
}
My problem is that some of the posts now have a second price, also stored as a meta key/value. The name of the new price is “price_used” and this price has its own updated key/value named “price_used_updated”.
I would like to modify the function my_search_price() so that it also handles the new price. The result should be posts where “price” OR “price_used” is between minprice and maxprice. “price_updated”https://wordpress.stackexchange.com/”price_used_updated” should be max 14 days old.
As I understand from reading some other posts, meta_query cannot be nested. So I need to modify the SQL instead using the posts_join or/and posts_where filter.
Can someone please point me in the right direction to the solution please? I’m guessing I’m not the first one that needs a more advanced meta_query. But I have never modified the SQL-query before.
What I have done is reading these these pages:
- Nested Meta Query with Multiple Relation Keys – WPSE Thread
- WordPress Codex: Custom Queries
UPDATE: The result query should look like this:
SELECT SQL_CALC_FOUND_ROWS wp_posts.*, mtGP2.meta_value, mtAM2.meta_value
FROM wp_posts
INNER JOIN wp_postmeta AS mtGP1 ON (wp_posts.ID = mtGP1.post_id)
INNER JOIN wp_postmeta AS mtGP2 ON (wp_posts.ID = mtGP2.post_id)
INNER JOIN wp_postmeta AS mtAM1 ON (wp_posts.ID = mtAM1.post_id)
INNER JOIN wp_postmeta AS mtAM2 ON (wp_posts.ID = mtAM2.post_id)
WHERE 1=1
AND wp_posts.post_type="post"
AND wp_posts.post_status="publish"
AND
(
(mtGP1.meta_key = 'price' AND CAST(mtGP1.meta_value AS SIGNED) BETWEEN 1 AND 10
AND mtGP2.meta_key = 'price_updated' AND CAST(mtGP2.meta_value AS SIGNED) > NOW()-60*60*24*14)
OR
(mtAM1.meta_key = 'price_used' AND CAST(mtAM1.meta_value AS SIGNED) BETWEEN 1 AND 10
AND mtAM2.meta_key = 'price_used_updated' AND CAST(mtAM2.meta_value AS SIGNED) > NOW()-60*60*24*14)
)
GROUP BY wp_posts.ID
But this query has two problems.
1) It is very slow
2) I still have a problem, because I don’t know how implement the query in wordpress. I think I need to use the post_where and posts_join filters, but I’m not sure how.
UPDATE 2. I’ve rewritten the query so it is no longer slow. But I still don’t know how to use filters or actions (like in my original my_search_price() function) to implement the query in wordpress.
SELECT SQL_CALC_FOUND_ROWS *
FROM
(
SELECT wp_posts.*
FROM wp_posts
INNER JOIN wp_postmeta AS mtGP1 ON (wp_posts.ID = mtGP1.post_id)
INNER JOIN wp_postmeta AS mtGP2 ON (wp_posts.ID = mtGP2.post_id)
WHERE 1=1
AND wp_posts.post_type="post"
AND wp_posts.post_status="publish"
AND mtGP1.meta_key = 'price'
AND CAST(mtGP1.meta_value AS SIGNED) BETWEEN 1 AND 10
AND mtGP2.meta_key = 'app_updated'
AND CAST(mtGP2.meta_value AS SIGNED) > UNIX_TIMESTAMP()-60*60*24*14
GROUP BY wp_posts.ID
UNION
SELECT wp_posts.*
FROM wp_posts
INNER JOIN wp_postmeta AS mtGP1 ON (wp_posts.ID = mtGP1.post_id)
INNER JOIN wp_postmeta AS mtGP2 ON (wp_posts.ID = mtGP2.post_id)
WHERE 1=1
AND wp_posts.post_type="post"
AND wp_posts.post_status="publish"
AND mtGP1.meta_key = 'price_used'
AND CAST(mtGP1.meta_value AS SIGNED) BETWEEN 1 AND 10
AND mtGP2.meta_key = 'price_used_updated'
AND CAST(mtGP2.meta_value AS SIGNED) > UNIX_TIMESTAMP()-60*60*24*14
GROUP BY wp_posts.ID
) AS t
1 Answer
Here is a rough outline of how you’d go about making a UNION work with WP_Query.
add_filter(
'posts_request',
function ($clauses) {
$clauses = str_replace('SQL_CALC_FOUND_ROWS','',$clauses,$scfr);
$scfr = (0 < $scfr) : 'SQL_CALC_FOUND_ROWS' : '';
$clause2 = $clauses; // manipulate this
return "SELECT {$scfr} u.* FROM (({$clauses}) UNION ({$clause2})) as u";
},
1,
2
);
Set up your WP_Query arguments to generate the first half of the UNION then manipulate that to create the whole query. Something like this should be close.
add_action('pre_get_posts', 'my_search_price');
function my_search_price( $query ) {
if ($query->get('maxprice') != "" && $query->get('minprice') != "" && $query->is_main_query()) {
$maxprice = intval($query->get('maxprice'));
$minprice = intval($query->get('minprice'));
$meta = array();
$meta[] =
array (
'key' => 'price',
'value' => array($maxprice,$minprice),
'compare' => 'between',
);
$meta[] =
array (
'key' => 'app_updated',
'value' => time()-(60*60*24*14),
'compare' => '>',
'type' => 'numeric'
);
$query->set('meta_query', $meta);
}
}
add_filter(
'posts_request',
function ($clauses) {
$clauses = str_replace('SQL_CALC_FOUND_ROWS','',$clauses,$scfr);
$scfr = (0 < $scfr) ? 'SQL_CALC_FOUND_ROWS' : '';
$clause2 = str_replace('price','price_used',$clauses); // manipulate this
$clause2 = str_replace('app_updated','price_used_updated',$clauses);
return "SELECT {$scfr} u.* FROM (({$clauses}) UNION ({$clause2})) as u";
},
1,
2
);
You will need to add logic to the post_requests filter so that it does not run when you don’t want it to.
