Query a Custom Post Type using SELECT that has ACF fields to compare dates

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!

Read more here:: Query a Custom Post Type using SELECT that has ACF fields to compare dates

Leave a Reply

Your email address will not be published. Required fields are marked *