Retrieving unique results in Django queryset based on column contents

Question

I am not sure if the title makes any sense but here is the question.

Context: I want to keep track of which students enter and leave a classroom, so that at any given time I can know who is inside the classroom. I also want to keep track, for example, how many times a student has entered the classroom. This is a hypothetical example that is quite close to what I want to achieve.

I made a table Classroom and each entry has a Student (ForeignKey), Action (enter,leave), and Date.

My question is how to get the students that are currently inside (ie. their enter actions' date is later than their leave actions' date, or don't have a leave date), and how to specify a date range to get the students that were inside the classroom at that time.

---

Edit: On better thought I should also add that there are more than one classrooms.

my first attempt was something like this:

students_in = Classroom.objects.filter(classroom__exact=1, action__exact='1')
students_out = Classroom.objects.filter(classroom__exact=1, action__exact='0').values_list('student', flat=True)
students_now = students_in.exclude(student__in=students_out)

where if action == 1 is in, 0 is out.

This however provides the wrong data as soon as a student leaves a classroom and re-enters. She is listed twice in the students_now queryset, as there are two 'enters' and one 'leave'. Also, I can't check upon specific date ranges to see which students have an entry date that is later than their leave date.

Answer 1

To check a field based on the value of another field, use the F() operator.

from django.db.models import F
students_in_classroom_now = Student.objects.filter(leave__gte=F('enter'))

To get all students in the room at a certain time:

import datetime
start_time = datetime.datetime(2010, 1, 21, 10, 0, 0) # 10am yesterday
students_in_classroom_then = Student.objects.filter(enter__lte=start_time,
                                                    leave__gte=start_time)

Answer 2

Django gives you the Q() and F() operators, which are very powerful and enough for most of the situations. However I don't think that it will be enough for you. Let's think about your problem at the SQL level.

We have something like a table Classroom ( action, ts, student_id ). In order to know which students are at the classroom right now, we would have to make something like:

with ( /* temporary view with last user_action */
  select action, max(ts) xts, student_id
  from Classroom
  group by action, student_id
) as uber_table
select a.student_id student_id
from uber_table a, uber_table b
where a.action = 'enter'
  /* either he entered and never left */
  and (a.student_id not in (select student_id from uber_table where action = 'leave')
    /* or he left before he entered again, so he's still in */
    or (a.student_id = b.student_id and b.action = 'leave' and b.xts < a.xts))

This is, I believe, standard SQL. However, if you're using SQLite or MySQL as database backends (most likely you are), then stuff like the WITH keyword for creating temporary views probably isn't supported and the query will just have to get even more complex. There may be a simpler version but I don't really see it.

My point here is that when you get to this level of complexity, F() and Q() become inadequate tools for the job, so I'd rather recommend that you write the SQL code by hand and use Raw SQL in Django.

Should you need to use the more common data access APIs, you should probably rewrite your data model in the way @Daniel Roseman implied.

By the way, a query for getting people that were inside the classroom in the same interval is just like that one, but all you have to do is limit the last leave ts to the beginning of the interval and the last enter ts to the end of the interval.