A Tip on Complex Django Quieries

by @jehiah on 2008-04-24 13:59
Filed under: 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.

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

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

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

You would expect this

[sql]
WHERE (author = 'A1' and datetime between start and end) 
OR (author = 'A2' and datetime between start2 and end)
[/sql]

However it really yields this

[sql]
WHERE author = 'A1' OR author = 'A2' OR datetime between start and end 
OR datetime between start2 and end2
[/sql]

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

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

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

Subscribe via RSS ı Email
© 2016 - Jehiah Czebotar