Home Forums ACF PRO Searching for a Relationship Match in Sub-Query


Searching for a Relationship Match in Sub-Query

  • Howdy!

    I’m new here despite owning ACF Pro for years I’m only now actually developing with it. While I have a brute-force solution to this issue which I will show below, I’m hoping the community may have experience with a better option. I did try to search for a solution here but the JavaScript kept causing the results page to hang so I decided to jump right in.

    I developed a site for someone who reviews movies. Because each post could reference multiple films and the films themselves could share titles (and even years) I have create a Films custom post type. Using ACF I created a relationship on Posts to relevant Films. More than one post may reference the same film as he writes new reviews of movies he’s written about previously. I am now developing an AJAX search for the site that looks only at the Film title and retrieves the 10 most likely matches along with the Film’s poster image (part of the Film ACF group) and the most recent Post.

    I am primarily a SQL Server developer. I would have created a bridge table which would join things as efficiently as possible. Coming from there my knowledge of MySQL’s special methods dealing with JSON is as extensive as knowing they exist 😛 I have written and tested the following MySQL but know my method of searching the JSON array in the meta_value is as clumsy as possible.

        poster.guid As posterURL,
             wp_postmeta Inner Join
             wp_posts On wp_postmeta.post_id = wp_posts.ID
             wp_postmeta.meta_key = 'vern_films' And
             wp_postmeta.meta_value Like Concat('%"', Cast(film.ID As VarChar(20)), '"%') And
             wp_posts.post_status = 'publish' And
             wp_posts.post_type = 'post'
         Order By
             wp_posts.post_date Desc
         Limit 1) As recentPost
        wp_posts film Inner Join
        wp_postmeta On film.ID = wp_postmeta.post_id Inner Join
        wp_posts poster On wp_postmeta.meta_value = poster.ID
        film.post_title Like Concat('%', :sterm, '%') And
        wp_postmeta.meta_key = 'vf_poster' And
        film.post_type = 'film' And
        film.post_status = 'publish'
    Order By
            When film.post_title Like Concat(:sterm, '%') Then 1
            When film.post_title Like Concat('%', :sterm) Then 3
            Else 2
    Limit 10

    Is there a MySQL JSON function that would be more performant than

    wp_postmeta.meta_value Like Concat('%"', Cast(film.ID As VarChar(20)), '"%')

    In case anyone brings it up, yes, I’d prefer to pull the actual permalink but as far as I can tell that would require looping and thus 10 additional SQL queries. If someone wants to make the case for using a standard get_posts() or WP_Query() I’m happy to consider but from what I’ve read it’s not possible to search just titles without a function filter() and there’s no way to get the sort in this manner. Plus those methods seem much more database heavy.


  • I can’t help you much with the query to directly query the DB. That would be over my head. I could probably figure it out but I prefer easier solutions.

    For something like this I would create an acf/save_post action. In my filter I would get the values from the related posts that I want to base a search on and store them in WP custom fields connected to the posts I want to search. Here is an only explanation of doing that The link deals with repeater fields but it can easily be applied to this case as well.

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

You must be logged in to reply to this topic.