> What I want is "every articles which have at least one photo"
> (and the possibility to add more filter after that,
> eventually).
>
>
> Currently, I'm using something like :
>
> Article.objects.filter(photos__in=Photo.objects.all())
>
> but this is horribly inefficient, since there is more than
> 40000 photos in the database
You are correct that that's the straightforward way to do it, but
as you've discovered, anything more than a handful of Photo
objects, and the SQL gets crazy.
Fortunately, Django's ORM lets you get at the underlying SQL via
a call to .extra() where you can provide your own WHERE clause.
This would look something like
Article.objects.extra(where="""
app_article.id in (select article_id from app_photo)
""")
You'd have to adjust for the various column-names and table-names
accordingly.
This also assumes that you have a one-to-many relationship
between photos and articles (a single photo doesn't appear on
multiple articles) or otherwise you'd have to adjust the query to
go through a joining table.
The efficiency of IN vs EXISTS can be debated and really depends
on your backend DB, but if you find performance problems, it can
be a good place to start your tweaking:
where="""EXISTS (
select 1
from app_photo
where article_id = app_article.id
)"""
Hope this helps,
-tkc
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
