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)
- relationship field (“related_artist_profiles” that relates to the Artist Profile Post Type)
- checkbox field (“winner” with one value of “yes” if it is checked meaning the current nominations was the winner)
- nominee (“nominee” text field, not relevant other than to illustrate the sql query below)
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.
thanks!
