How to select objects referenced by another table ?

Showing 1-4 of 4 messages
How to select objects referenced by another table ? Jonathan Ballet 7/17/07 9:49 AM
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

Re: How to select objects referenced by another table ? Tim Chase 7/17/07 11:29 AM
> 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


Re: How to select objects referenced by another table ? Tim Chase 7/17/07 11:31 AM
> 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

Re: How to select objects referenced by another table ? Jonathan Ballet 7/18/07 1:14 AM
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 !