Searching for a Relationship Match in Sub-Query

• clubside

• July 2, 2022 at 8:12 pm

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.

Select
    film.ID,
    film.post_title,
    film.post_name,
    poster.guid As posterURL,
    (Select
         wp_posts.guid
     From
         wp_postmeta Inner Join
         wp_posts On wp_postmeta.post_id = wp_posts.ID
     Where
         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
From
    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
Where
    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
    Case
        When film.post_title Like Concat(:sterm, '%') Then 1
        When film.post_title Like Concat('%', :sterm) Then 3
        Else 2
    End
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.

Thanks!

• John Huebner

• July 3, 2022 at 9:52 am

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 https://web.archive.org/web/20190814230622/https://acfextras.com/dont-query-repeaters/. The link deals with repeater fields but it can easily be applied to this case as well.