4

I'm implementing a achievement system. One of the "badges" I'm trying to create will determine:

  1. If a user has joined in at least one coding challenge
  2. Then hasn't joined in 3 consecutive coding challenges
  3. Then started participating again.

The badge is simply called "I'll be back" ;-)

The tables

users
==================
id    fullname
1     Gary Green


challenge
==================================
id  name         start_date
1   challenge1   01-AUG-2010
2   challenge2   03-AUG-2010
3   challenge3   06-SEP-2010
4   challenge4   07-SEP-2010
5   challenge5   30-OCT-2010
6   challenge6   05-NOV-2010


entries
====================================================
id   challengeid    userid    type       code
1     1             1         1          -
2     2             1         1          -
3     6             1         1          -
4     6             1         2          -

The "type" in the entries table refers to if the entry type is either a non-regex based entry or regex based one. A user can submit both a regex and non-regex entry, therefore the above entry for challenge 6 is valid.

Example output

This is the style output of the query I would like (in this case the badge should be awarded):

(for userid 1)
Challenge 1 --> Joined in
Challenge 2 --> Joined in
Challenge 3 --> NULL
Challenge 4 --> NULL
Challenge 5 --> NULL
Challenge 6 --> Joined in

How?

Here are my questions

  1. Whats the best way to do this in a query?
  2. Is there a function I can use in MySQL to SELECT this range without resorting to some PHP?

The query so far

I'm doing a LEFT OUTER JOIN to join the challenge table and entries table (LEFT OUTER to make sure to preserve the challenges the user has not joined in), then sort by challenge start_date to see if the user has not joined in for 3 or more consecutive challenges.

SELECT challenge.id AS challenge_id, entries.id AS entry_id
FROM challenge_entries entries
LEFT OUTER JOIN challenge_details challenge
 ON entries.challengeid = challenge.id
WHERE entries.userid = <user_id>
ORDER BY challenge.start_date
GROUP BY entries.challengeid

important edit: for this badge to make sense the criteria will need to be 3 or more consecutive challenges sandwiched between challenges that were joined in i.e. like the example output above. Otherwise anyone who joins in a challenge for the first time will automatically receive the badge. The user has to be seen to have been "away" from participating in challenges for a while (>=3)

Lightness Races in Orbit
  • 378,754
  • 76
  • 643
  • 1,055
Gary Green
  • 22,045
  • 6
  • 49
  • 75

1 Answers1

1

I think you need to use the other table to start from...

SELECT challenge.id AS challenge_id, entries.id AS entry_id FROM challenge_details challenge
LEFT JOIN challenge_entries entries ON entries.challengeid = challenge.id and entries.userid = <user_id>
ORDER BY challenge.start_date

adding a group by can be done as you want...

Kennethvr
  • 2,660
  • 5
  • 26
  • 35
  • Thanks for this. That does seem to fix the query, however I can't get the `GROUP BY entries.challengeid` to work, it just outputs nothing. – Gary Green Nov 10 '10 at 13:17
  • why do you do a group by on challengeid on the entries table, can't you do a group by on the id of the challenge table? In your case Group by challenge.id – Kennethvr Nov 10 '10 at 13:23
  • Oh silly me I had my group slightly mixed up, thanks its grouping it perfectly now. My final question is 2. is there a good way to check if there are 3 NULLs sandwiched between two challenges participated in? I thought about converting the data into a single string then doing a string regex check like /(\d+) (NULL){3} (\d+)/ seems the best bet? – Gary Green Nov 10 '10 at 13:45
  • if you want to remove the null I would advice to add it to your query without conversions.. are you sure you need a left join? perhaps more info is needed on what result you want with that. If you want to remove the NULL rows, try using INNER JOIN in stead of LEFT JOIN. – Kennethvr Nov 10 '10 at 13:59
  • I was just using NULL as data example. I've managed to get it working doing this method: `$data = ''; while ($row = mysql_fetch_row($r)) $data .= (!$row[0]) ? ('x') : ($row[0]); if (preg_match('/(\d+) (x x x) (\d+)/', trim($data, ' '))) { //award badge }` -- now this query works fine for a single user, if I wanted to work out across all entries what users can be awarded this badge, how would the query need to be changed? – Gary Green Nov 10 '10 at 14:52
  • group by userid before grouping on something else... I must say that NULL is the worst data example you can give. It does mean something and will change the way you want to work... – Kennethvr Nov 10 '10 at 14:55
  • Thanks for your help in this. Grouping by userid won't work because users can enter multiple challenges, I think I'll have to just call the above query for every user that has entered a challenge. – Gary Green Nov 10 '10 at 15:21
  • i hope the database isn't that large... this isn't the good way to do it! dont loop on queries but investigate on how you can change the query in order to do it at once... this case isn't that complicated, so I think you could do it. – Kennethvr Nov 10 '10 at 15:23
  • Well the database has around 500 entry records, so fairly small but I'm sure it would still take some time to perform the recursive queries. Basically to process the data I will need the query to output the challenges and if the user has participated in that challenge, then loop for the next user. So the query output would be like so: http://pastebin.com/KX1FF9gN -- that way I can loop through in PHP – Gary Green Nov 10 '10 at 15:29
  • Following on from my last comment, where the entry_id is blank it means the user did not participate. So the fields are first sorted by challenge_id, then user_id, grouped by challenge_id (because there can be a regex and non-regex type entries and we only want this to count as a single entry) – Gary Green Nov 10 '10 at 15:33