I’m having an issue and wondering if what I’m trying to do is possible (using ACF 5 PRO)
I’m having an issue while trying to run a large query that is constantly timing out the server (VPS with 2gb ram allocated to wordpress with the database on a separate 1gb server).
I have 2 post types that have a bunch of custom fields attached to them. I will list them below with the fields that are relevant to my issue
Artist Profiles (~3600 posts)
(no relevant fields, all relationships are made using nominations post type)
Nominations (~7000 posts)
I’m trying to generate a page that shows all artist profiles ranking by how many wins/nominations they have (so ordered by wins, nomination desc).
I’m wondering if its possible to formulate an SQL query that could do this? I have been trying for hours and haven’t come up with anything.
The reason I’m going with the SQL query because using a wp_query to query all artists profiles and then another query to get all the related nominations and then put into an array to be sorted and cached can’t be done because I keep timing out the server trying to run the query :/
For example lets say that I wanted to do something similar to this and rather than a relationship field to attach it to an artist profile I just use a simple text field called “nominee” to generate this page I could do something like this
SELECT meta1.meta_value AS nominee, count(meta1.meta_value) as nominations, count(meta2.meta_value) as wins FROM wp_posts as post Left JOIN wp_postmeta meta1 ON meta1.post_id = post.ID AND meta1.meta_key = 'nominee' Left JOIN wp_postmeta meta2 ON meta2.post_id = post.ID AND meta2.meta_key = 'winner' WHERE post.post_type = 'nomination' group by nominee ORDER BY wins DESC
Its proving to be infinitely more difficult however to group these using the relationship field ‘related_artist_profile’ as it is a serialized array. Since each nomination can have multiple related artists there might be multiple ids in that field. So i guess rather than matching the nomination name above you would need to match the related_artist_profile field using the ID of the artist post and check against the serialized array to see if the string contains the id ? I’m definitely getting over my head here in how to formulate this query.
The desired end result would be all artist profiles ranked from highest to lowest by how many nomination posts they have associated with them that have the winner field checked.
If anyone has any ideas I could use any help even if its just suggestions on how to approach this differently.
You must be logged in to reply to this topic.
Welcome to the Advanced Custom Fields community forum.
Browse through ideas, snippets of code, questions and answers between fellow ACF users
Helping others is a great way to earn karma, gain badges and help ACF development!
© 2022 Advanced Custom Fields.