Well i’ve skipped using ACF for now until I get a response but FYI, I done it using the following:
function sytycdv2_posts_clauses($pieces) {
global $wp_query, $wpdb;
if (preg_match("/post_type = '([^']+)'/", $pieces['where'], $matches) && !is_admin() && in_array($matches[1], get_campaign_post_types())) {
$pieces['fields'] .= ", COALESCE((SELECT CASE WHEN COUNT(p99.ID) = 2 THEN 1 ELSE 0 END FROM {$wpdb->posts} p99 INNER JOIN {$wpdb->postmeta} pm99 ON (p99.ID = pm99.post_id) WHERE p99.post_type = 'campaign' AND (pm99.meta_key = '_start_date' AND (CAST(pm99.meta_value AS DATETIME) IS NULL OR CAST(pm99.meta_value AS DATETIME) <= NOW())) OR (pm99.meta_key = '_end_date' AND (CAST(pm99.meta_value AS DATETIME) IS NULL OR CAST(pm99.meta_value AS DATETIME) > NOW())) GROUP BY p99.ID HAVING COUNT(p99.ID) = 2 AND FIND_IN_SET(p99.ID, pm98.meta_value) LIMIT 0, 1),0) AS s";
$pieces['join'] .= " LEFT JOIN {$wpdb->postmeta} pm98 ON (wp_posts.ID = pm98.post_id AND pm98.meta_key = '_campaigns')";
$pieces['orderby'] = 's DESC, ' . $pieces['orderby'];
}
return $pieces;
}
add_action( 'posts_clauses', 'sytycdv2_posts_clauses' );
As dirty as it looks, the above basically uses a subquery to return all campaigns that are valid and then checks to see if that campaign ID exists in the current posts custom field.
If the value exists, the subquery will return 1 otherwise 0.
Then you can see I’m sorting by the subquery result followed by the original sort, allowing it to promote the ones I want at the top, followed by the original posts 🙂
DIIIIIIIIRTY yes, but it works for now.
