WordPress.org

Ready to get started?Download WordPress

Forums

How to reduce queries? (3 posts)

  1. ianwp
    Member
    Posted 1 year ago #

    Hi!
    I'm looking for a way to save database queries on one of my sports sites.

    This is how it works currently.

    There are 2 custom types involved here. One is "competitors" and other is "tournaments".

    Then there is a ranking based on how many points each competitor received on each tournament.

    What I did was adding a custom meta box on the "tournaments" post type that allows to write a value for each person that participated in (these can be added dinamycally to the meta box).

    Now, on the rankings page, I need to loop through each posted competiror and then get the total value they have on each tournament (which I now do by doing another wp_query using the corresponding meta query).

    This means that each person requires a wp_query. Now it's not a problem (only 80 registeed competitors), but if more keeps being added, the number of queries will be huge.

    Also, since I cannot know the position in the ranking until I have added everything for everyone, a php paging would be imposible (only a jquery one that doesn't make a difference here).

    Any ideas?

    Thanks!!

    The meta key is something like competitor_X (X being the post id of the competitor)

  2. vtxyzzy
    Member
    Posted 1 year ago #

    You might be able to do a first query on wp-postmeta like this:

    $sql = "SELECT meta_key, SUM(meta_value) as point_total
    FROM $wpdb->postmeta
    WHERE meta_key LIKE 'competitor_%'
    GROUP BY meta_key
    ";
    $totals = $wpdb->get_results($sql);
    $totals_indexed = array();
    foreach ($totals as $total) {
       $totals_indexed[$total->meta_key] = $total->point_total;
    }

    Then as you loop through the competitors, you can construct their key (competitor_X) and look up their total in $totals_indexed.

  3. ianwp
    Member
    Posted 1 year ago #

    That's a great "outside the box" coding idea (at least for my way of doing stuff, I tend to rely too much on WP_Query)!

    I will try to adapt that to the code I already have, which won't be an easy task since I'm working with multiple values for the same meta_key :/

    Thanks for your answer!

Topic Closed

This topic has been closed to new replies.

About this Topic