Django Subqueries in WHERE clause

For most cases, the Django ORM does a fine job of handling the queries I want. Sometimes I run into difficult queries and I get a little discouraged because I don't want to break all the way out of the model and use raw SQL. I want to stay in the framework and still get the results I neeed. I ran into one of those situations today and found out about QuerySet's extra() method.

Let me give you a little background on our system before I go further. The central and most critical piece of information we store are User Profiles. Profiles have gender and sexual preference attributes which are the most important two attributes for generating possible matches of people. The possible matches get voted on by anyone interested in playing matchmaker and these votes get tallied constantly. Weekly, each user will have a report of their top matches generated for them. It's simple until you start trying to filter out matches based on who has blocked who.

The options here are to do the filtering programatically after the results are returned or to do some special SQL. I opted for doing some SQL subqueries which required an understanding of Django that I didn't have before. Essentially, you can modify the SELECT statement that Django generates for QuerySets by using the extra() method. The where parameter of that method takes a list of strings, then ANDs them together into the WHERE clause of the query.

So I could write code like this:

sbuquery1 = 'profile2 NOT IN (SELECT blocked_user FROM blockuser WHERE blocker = %d)' % self.id
matches = Match.objects.filter(profile1 = self.id).extra(where=[subquery1])

And the resulting SQL would look something like this:

SELECT * FROM match WHERE profile1 = 1 AND profile2 NOT IN (SELCT blocked_user FROM blockuser WHERE blocker = 1)

I'm leaving out a lot of details like what fields the tables contain and that Django will put every column name into the SELECT statement so the SQL statement is much longer than what I have above, but I didn't want to clutter up this example with all that. There are other examples that I found that go into more detail that I have.

These two links had some valuable information for me:
Kyle Fox's : Minimize database queries using Django’s QuerySet.extra()
Django snippets: Custom SQL via subquery

Yumbunny, Who looks good with you?

We're busy working on a great new way to meet people and a fun way to that you can help be a matchmaker yourself.

More details coming soon.