Support

Account

Home Forums Add-ons Repeater Field Query to retrieve fields in same row in repeater

Helping

Query to retrieve fields in same row in repeater

  • (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.

  • 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')
Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.