Login

Querying on existence of a relationship

Author:
ubernostrum
Posted:
August 3, 2007
Language:
Python
Version:
.96
Tags:
models orm foreign-keys
Score:
8 (after 8 ratings)

When you have two models joined by a foreign key, it's common to want to retrieve a set of objects from the "target" of the foreign key based on whether there are any objects "pointing" to them. This snippet demonstrates how to do so, using the extra method of the default model manager.

Note that this is probably more efficient than using two ORM methods (e.g., selecting all values from one table, and using an id__in lookup on the other) since it does the whole thing in one query and avoids instantiating any intermediate objects.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
from django.db import backend, models


class Foo(models.Model):
    name = models.CharField(maxlength=250)


class Bar(models.Model):
    name = models.CharField(maxlength=250)
    foo = models.ForeignKey(Foo)


# Retrieve all Foo objects which have at least one                                                                                                                                                       
# Bar object referencing them:                                                                                                                                                                           
Foo.objects.extra(where=['id IN (SELECT %s FROM %s)' % (backend.quote_name('foo_id'), Bar._meta.db_table)])

# Retrieve all Foo objects which have zero Bar                                                                                                                                                           
# objects referencing them:                                                                                                                                                                              
Foo.objects.extra(where=['id NOT IN (SELECT %s FROM %s)' % (backend.quote_name('foo_id'), Bar._meta.db_table)])

More like this

  1. Automatically setup raw_id_fields ForeignKey & OneToOneField by agusmakmun 6 months, 2 weeks ago
  2. Crispy Form by sourabhsinha396 7 months, 1 week ago
  3. ReadOnlySelect by mkoistinen 7 months, 2 weeks ago
  4. Verify events sent to your webhook endpoints by santos22 8 months, 2 weeks ago
  5. Django Language Middleware by agusmakmun 9 months ago

Comments

Please login first before commenting.