A Tip on Complex Django Quieries

posted April 24th 2008 at 1359 EDT in All, Programming, Python, SQL, django

I like the Django database API a lot. It makes writing SQL statements quite clean. However, I ran into a little snag recently on something that I would expect to be different.

First a quick refresher for those who are not familiar with the Django API. Essentially you use a filter or get function to do all your queries.


post=Post.objects.get(title='python is great')
comments = Comment.objects.filter(post=post,author_name='Jehiah')
 

As you can see, all the parameters to the filter command get combined with a sql AND

Django also has a syntax for more complex queries using Q objects.

The problem I ran into was in grouping things together. I expected all the parameters passed to a Q object to be ANDd together just like they are in a filter method, and then I could OR those groups.

With this block of code


posts = Post.objects.filter( Q(author='A1',datetime__range=(start,end) |
Q(author='A2',datetime__range=(start2,end2)))
 

You would expect this


WHERE (author = 'A1' AND datetime BETWEEN start AND end)
OR (author = 'A2' AND datetime BETWEEN start2 AND end)
 

However it really yields this


WHERE author = 'A1' OR author = 'A2' OR datetime BETWEEN start AND end
OR datetime BETWEEN start2 AND end2
 

I don't really see the logic in not making it behave like a filter object does when it's passed multiple parameters.

Anyway, what I wanted to do needed code like this


posts = Post.objects.filter( (Q(author='A1') & Q(datetime__range=(start,end) )|
(Q(author='A2') & Q(datetime__range=(start2,end2))))
 

Which does not seem as clear to me. It makes use of the & operator to call __and__ between the two Q objects just like the | operator calls __or__ on two Q objects.

Just thought I'd share

3 Responses

  1. #1 Luke Plant
    2 years, 3 months ago

    I think that should be reported as a bug. But you can also do complex queries just using QuerySet objects:


    Post.objects.filter(author='A1',datetime__range=(start,end)) |
    Post.objects.filter(author='A2',datetime__range=(start2,end2))

    which produces the expected SQL.

  2. #2 Luke Plant
    2 years, 3 months ago

    BTW, your comment box doesn’t work with Konqueror — the other fields don’t appear when I click on the textarea, like they do in Firefox

  3. #3 jehiah
    2 years, 3 months ago

    @Luke, thanks for the tip on Konqueror, and for pointing out that QuerySets can be treated in that same way.