Support

Account

Home Forums General Issues Order query based on relationship

Solving

Order query based on relationship

  • Hi all,

    I’m currently working on a website that uses ACF and I’m looking for a way of promoting posts to appear first which have a relationship with another post type and that post type has certain meta value.

    Basically I have events, competitions, videos and posts all of which have an ACF relationship with campaigns. Campaigns have two ACF custom date fields which basically suggest when that campaign is valid between.

    When WordPress displays a list of posts, events, competitions and video’s I am looking to promote those that have a valid campaign to the top of the list.

    Without completely rewriting all of the queries that WordPress uses, I was hoping there was a filter I could use?

    Any suggestions would be extremely appreciated 🙂

    Thanks

    Gav

  • 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.

  • Hi @groberts

    Yes ACF isn’t a querying motor in any way really so sure, anything you can do with wp_query you can use ACF for. Everything else and as advanced as that query you’ll have to do it yourself.

    Feel free to mark your second post as the answer so we know this topic is closed 🙂

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.