I want to create a QuerySet object which select objects from a table
which a referenced by another table, but I can't find how to do that.
Here is an example :
class Article(models.Model):
[...]
class Photo(models.Model):
article = models.ForeignKey(Article)
[...]
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 (and PostgreSQL doesn't accept this query
either, but this is another problem ...)
How can I do that ?
Thanks for your answer,
- Jonathan
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
Whoops...forgot that where="" takes a list of strings, that should be
where=["..."]
-tkc
Argl, I missed the 'extra' method in the documentation, thanks a lot !