0

So I have a CPT called ads that has ACF fields of ad_start_date and ad_end_date. I am trying to write a custom WP query using a SELECT statement instead of using WP_Query because I am return an ajax request so I need to choose exactly the fields to send over.

So for a test I did use the WP_Query method and it works perfectly. But in order to get acf fields, or say Featured Image this WP Query method will need to be on a template php file. I need a custom SELECT statement so I can choose exactly what fields I need to send over to the JavaScript side in one json response.
FYI, this function retrieves an array of ids from a previous AJAX request response thus you see the 'post_in' argument. But again the below function works perfectly, but cannot use it since a response from WP_Query is limited and needs the WP Loop or the post ID to pass in to the ACF

get_field('ad_start_date', post->ID) 

function. I need this query to get everything on this one query call to send back as json response.

function get_ads() {
    $id = $_POST['ids'];
    $integerIDs = json_decode( $id, true);
    $todays_date = date('Ymd', strtotime("now"));
    $newargs = array(
        'numberposts'   => -1,
        'post_type'     => 'ads',
        'post__in'      => $integerIDs,
        'post_status'   => 'publish',
        'meta_query'    => array(
            array(
                'key'       => 'ad_start_date',
                'compare'   => '<',
                'type'      => 'numeric',
                'value'     => $todays_date,
                ),
            array(
                'key'       => 'ad_end_date',
                'value'     => $todays_date,
                'compare'   => '>',
                'type'      => 'numeric',
            ),
        ), 
    );
    $the_final_query = new WP_Query( $newargs );
    echo json_encode($the_final_query);
    wp_die();
}

So here are my attempts at using the Custom mysql statement in mysql workbench to get this data. But I am not having any success. Does anyone understand where my issue is?

SELECT * FROM wp_posts
INNER JOIN wp_postmeta ON wp_postmeta.post_id = wp_posts.ID
AND( wp_postmeta.meta_key = 'ad_start_date' AND wp_postmeta.meta_value < 
20190620)  
AND ( wp_postmeta.meta_key = 'ad_end_date' AND wp_postmeta.meta_value > 
20190620)
WHERE wp_posts.post_type = 'ads'
AND wp_posts.post_status = 'publish'

Second attempt trying to cast to number since meta_value is longtext data type

SELECT * FROM wp_posts
INNER JOIN wp_postmeta ON wp_postmeta.post_id = wp_posts.ID
AND( wp_postmeta.meta_key = 'ad_start_date' AND (CAST(wp_postmeta.meta_value 
AS 
UNSIGNED) < 20190620) ) 
AND ( wp_postmeta.meta_key = 'ad_end_date' AND (CAST(wp_postmeta.meta_value 
AS 
UNSIGNED) > 20190620) )
WHERE wp_posts.post_type = 'ads'
AND wp_posts.post_status = 'publish'

Any help would be greatly appreciated!

0

You can output the exact SQL query generated by WP_Query using

$the_final_query->request

This should give you the starting point you need to customize your SQL

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.