How to select objects referenced by another table ?

[Jonathan Ballet]

Hello,

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

[Tim Chase]

> 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

[Tim Chase]

> 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)
> """)

Whoops...forgot that where="" takes a list of strings, that should be

where=["..."]

-tkc

[Jonathan Ballet]

On 17 juil, 20:29, Tim Chase <django.us...@tim.thechases.com> wrote:
> 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.

Argl, I missed the 'extra' method in the documentation, thanks a lot !