2

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:

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
  • I am not exactly sure the logic that you are looking for. There are all kinds of ways to put together AND and OR conditions on complicated queries. Can you clarify? – s_ha_dum Sep 19 '13 at 15:26
  • I've updated my question. – PappApp Sep 22 '13 at 10:42
  • Why are you joining on wp_term_relationships? You can't ORDER BY MAX. MAX will return the highest value which is not likely to be a column name. You will need even more complicated SQL to make that work. How fast is the query without the GROUP BY and the ORDER BY? – s_ha_dum Sep 22 '13 at 14:50
  • I shouldn't be joining on wp_term_relationships. I've updated my original questions. Thanks. I've also removed ORDER BY MAX. I'm not sure why it's not working. When removing ORDER BY the query takes 315 seconds. When removing ORDER BY and GROUP BY the query takes 213 seconds (but returned the wrong result, too many rows) – PappApp Sep 22 '13 at 18:46
  • I told you why ORDER BY MAX doesn't work. Are those numbers better or worse than with the ORDER BY and GROUP BY? Should be better. Sort the results in PHP and you will still get a better time than having MySQL do it. You should be able to construct that query with WP_Query plus some filters but the query itself is nightmarish. To improve performance you will need to get very, very good with SQL, which makes the question off-topic here, or rethink how your data is stored. – s_ha_dum Sep 22 '13 at 19:02
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.