Query to retrieve fields in same row in repeater

• acasla

• October 30, 2020 at 9:23 am

(I already asked this in Stackoverflow since it seems to be a MySQL question, but I got no responses and I really need it).

I am working on a WordPress site that uses the Advanced Custom Field plugin and the Advanced Custom Field repeater plugin, so I cannot change the database structure.

The custom fields (and their values) for each post are stored in a table called post_meta with the following fields:

meta_id (autoincrement), post_id, meta_key, meta_value

For regular custom fields, it’s easy. Let’s say post 200 has “John Doe” for field “full_name”, then it would be stored like this:

xxx, 200, full_name, John Doe

The problem is when the field has a subset of repeated fields, the way it is stored is a bit more complicated. For the meta_key, the value is {fieldName}_{rowNumber}_{subfieldName}.

For example, in this WordPress what I am trying to do is to store the players of a team that participatd in a certain match, and how many minutes they played that match (here is an screenshot):

The general field is called planilla (players sheet), so every value is stored like this:

planilla_1_jugador, planilla_1_minutos (player_id and minutes played for the first row)

**So, my problem starts when I want to count all the matches played by a player.** I could count all the rows in post_meta where meta_value is my player id, but that would count matches where the player played 0 minutes (and I don’t want to). This is the query that does that, by the way:

SELECT COUNT(*) as total from $wpdb->postmeta INNER JOIN $wpdb->posts ON ( $wpdb->posts.ID = $wpdb->postmeta.post_id ) WHERE( $wpdb->postmeta.meta_key LIKE 'planilla_%_jugador' AND $wpdb->postmeta.meta_value = {player_id} ) AND $wpdb->posts.post_type = 'partido' AND (($wpdb->posts.post_status = 'publish'))

If I want to check for the played minutes, I would need to add a join that matches with the same position in the players sheet (planilla). If the table would have an extra column called meta_key_index, that would store the number, it would be as easy as this:

SELECT COUNT(*) as total from wp_postmeta pm1 INNER JOIN wp_posts ON ( wp_posts.ID = pm1.post_id )
        INNER JOIN wp_postmeta pm2 ON (pm1.post_id = pm2.post_id)
        WHERE( pm1.meta_key LIKE 'planilla_%_jugador' AND pm1.meta_value = 420 )
        AND (pm2.meta_key LIKE 'planilla_%_minutos_jugados' and pm2.meta_value > 0)
        AND (pm1.meta_key_index = pm.meta_key_index)
        AND wp_posts.post_type = 'partido' AND ((wp_posts.post_status = 'publish')

But it hasn’t, so I need to do the join using a regular expression or something like that. I thought of several things but nothing seem to make it possible to work.

What I am doing right now, I doing 20 queries (max amount of players per sheet) where the key is hardcoded, so instead o doing planilla_%_jugador and I am querying planilla_1_jugador, and so on, and then summing all the results at the end. But the 20 queries are slow and I’d prefer to solve everything doing just one query.

I hope this post makes sense and thanks in advance.

• mohitbhagat92

• October 28, 2021 at 11:07 am

I was stuck on a similar issue, thanks for your post I solved it with a little modification

SELECT COUNT(*) as total from wp_postmeta pm1 INNER JOIN wp_posts ON ( wp_posts.ID = pm1.post_id )
        INNER JOIN wp_postmeta pm2 ON (pm1.post_id = pm2.post_id)
        WHERE( pm1.meta_key LIKE 'planilla_%_jugador' AND pm1.meta_value = 420 )
        AND (pm2.meta_key LIKE 'planilla_%_minutos_jugados' and pm2.meta_value > 0)
        AND (REPLACE(pm1.meta_key,'_jugador','') = REPLACE(pm2.meta_key,'_minutos_jugados',''))
        AND wp_posts.post_type = 'partido' AND ((wp_posts.post_status = 'publish')