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!
