WordPress.org

1. matthewfdeville
   Member
   Posted 5 months ago #

   We have a site that will have 7 challenges. Users can submit their times and scores once for each challenge.

   Their submissions will be stored in the wp_posts table of the database as custom posts

   The scores for each submission will be stored in the wp_post_meta table as key/value pairs.

   Finally, we will have a page that will display all user and their scores for all seven challenges and rank them based on the cumulative score value alone.

   Now, WP has built in functions to avoid directly querying the database, but I don't think they will work here. This is an example of the SQL output from the WP_Query class to pull the posts from challenge_one:

   SELECT * FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'challenge_one' AND (wp_posts.post_status = 'publish') ORDER BY wp_posts.post_date DESC LIMIT 0, 10;

   I know there needs to be a JOIN to the wp post meta table to pull in that data. But, since I'm still green in the SQL world, I'm not sure how to calculate the points and then sort the whole list.

2. matthewfdeville
   Member
   Posted 5 months ago #

   messing around, I've got this far:

   SELECT p . * , s . *
   FROM wp_posts p
   JOIN wp_postmeta s ON p.id = s.post_id
   WHERE 1 =1
   AND p.post_type =  'challenge_one'
   OR p.post_type =  'challenge_two'
   OR p.post_type =  'challenge_three'
   AND (
   p.post_status =  'publish'
   )
   ORDER BY p.post_date DESC

3. matthewfdeville
   Member
   Posted 5 months ago #

   this seems to be getting close:

   SELECT p . * , s . *
   FROM wp_posts p
   JOIN wp_postmeta s ON s.meta_key = 'score_value'
   WHERE p.id = s.post_id
   AND 1 = 1
   AND p.post_type =  'challenge_one'
   OR p.post_type =  'challenge_two'
   OR p.post_type =  'challenge_three'
   AND (
   p.post_status =  'publish'
   )
   ORDER BY s.meta_key ASC;

   but the ordering doesn't seem to be right.

   anyone have any ideas?

4. matthewfdeville
   Member
   Posted 5 months ago #

   and the scores need to be added up across challenges. that's where I'm getting stuck...

Reply

You must log in to post.