Query posts by multiple meta keys not working when comparing with current date

0
votes

I am writing a code to query posts meta value based on other meta value compare date, below is my code, it query all posts ignoring the date condition:

i have tried to use 'LEFT JOIN" sentence with no success

$querystr = "
SELECT DISTINCT m2.meta_value as 'appdata'
FROM $wpdb->posts
INNER JOIN $wpdb->postmeta m1 ON ($wpdb->posts.ID = m1.post_id AND m1.meta_key LIKE 'repeater_%_date' AND m1.meta_value > NOW())
INNER JOIN $wpdb->postmeta m2 ON ($wpdb->posts.ID = m2.post_id AND m2.meta_key LIKE 'repeater_%_appdata' AND m2.meta_value != '')
WHERE $wpdb->posts.ID = m1.post_id
";

$pageposts = $wpdb->get_results($querystr, ARRAY_A);

The problem is this code query the old and new posts which has the date meta key

Note: repeater_%_date is ACF date field storing the date as Ymd (20190708)

Any solution please?

1
phpmysqlwordpressadvanced-custom-fieldsacfpro
have you tried it with WP_Query instead. – Akhilesh
Yes, i tried it, but it was slower since i have around 2500 post, wpdb get_results is much faster even its retrieving all posts, i think its better solution because i need to get only one meta value filtered by another one in same ACF repeater. – WPDeveloper
$wpdb->posts.ID = m1.post_id AND m1.meta_key LIKE 'repeater_%_date' AND m1.meta_value > NOW()) it seems like that these lines are not correct as you are specifying condition in it which should be where clause except $wpdb->posts.ID = m1.post_id . – Akhilesh

1 Answers

0
votes
$get_featured_args3  = array(
              'post_type'  => 'post',
              'orderby'     => 'date',
              'order'       => 'DESC',
              'posts_per_page'=>-1,       
              'meta_query' => array(
                    'relation' ->'AND',
                    array(
                        'key'     => 'key1',
                        'value'   => 1,
                        'compare' => '=',
                    ),
                    array(
                        'key'     => 'key2',
                        'value'   => 1,
                        'compare' => '=',
                    ),
                ),
                'date_query' => array(
                    array(
                        'year' => date('Y'),

                    ),
                ),
            );
    $get_featured_results3 = new WP_Query( $get_featured_args3 );

    $posts = $get_featured_results3->posts;
    foreach($posts as $post){

    }