How do I create my own nested meta_query using posts_where / posts_join?

Question

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"/"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

Answer 1

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.