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


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.

Viewing 1 post (of 1 total)

You must be logged in to reply to this topic.

We use cookies to offer you a better browsing experience, analyze site traffic and personalize content. Read about how we use cookies and how you can control them in our Cookie Policy. If you continue to use this site, you consent to our use of cookies.