post

I’ve done this before. I know I have. The concept isn’t difficult at all, but this query is giving me a headache. Before I change the way I’m doing this, I thought I’d post about it since that often helps me to think things through, overcoming the obvious.

So a user can take assessments on individual topics/sections of a course, which can then be used to make up a course grade (if desired). Anyway, it was decided recently, instead of updating a users score when a topic assessment is retaken, save all scores so they can see their progress. There are plenty of ways to do this, I realize, but I thought it may be easiest to insert a new record for that user/topic combination and just pull out the most recent (as this isn’t something you “rollback” to—at most, you’d just want to see previous scores, etc).

That brings me to this query. I need to get all of the topics for a course, group them (since there are now multiples), and only return the most recent of the group. Pretty simple, eh? I tend to agree. However, whether it be that I haven’t written explicit SQL in a while or I’m completely overlooking the obvious, I’m not getting what I need. I can get the record individually from the group, without the other topics; or I can get all the topics except the most recent of the group. So I thought about UNION, but that seems over-the-top for this. It’s hard to accept a monster query when I don’t think it should be monstrous. Here’s a dumbed down version of the setup:

## TABLE: courses (has_many topics)
 #   id, title, description, etc
## TABLE: topics (belongs_to course)
 #   id, course_id, title, description, etc
## TABLE: user_topics
 #   id, user_id, topic_id, score, completed_on, etc
 # ----------------------------------------------------
 # (note: topics can be infinitely nested with itself as 
 #  well, but that relationship shouldn't matter, here)

## SQL (1)
 # => returns everything except the most recent topic -
 #    in fact, it doesn't return any records within that 
 #    "group" of topics

 select topic_id, score, completed_on
 from user_topics ut
 inner join topics t on t.id = ut.topic_id
 where t.course_id = {course_id}
 group by ut.topic_id
 having ut.completed_on = max(ut.completed_on);

## SQL (2)
 # => same results as above

 select ut.topic_id, ut.score, ut.completed_on
 from (select topic_id, score, max(completed_on) as max_completed_on
       from user_topics
       group by topic_id) ut
 inner join user_topics u_t on u_t.topic_id = u_t.topic_id
 inner join topics t on t.id = ut.topic_id
 where t.course_id = {course_id} and ut.completed_on = max_completed_on;

Anyway, there are several other attempts, but no reason to list them. I don’t expect anyone to think about it, I was just hoping to clear my mind and give it another shot before handling this a different way. I suppose I could treat it as a “version” of a user_topic, but my stubborn side won’t let me move past this; the data is there, I just need to get it out. I remember when I used to LOVE writing SQL—I’m afraid those days are long gone.