Join tables in Django that share the same ForeignKey

Question

I have three classes I'd like to query. I would like to let the database do the work. Would it be possible to run a single query on the User object to get all the related fields that match? I am trying to avoid joining the three tables in my code. If I were to do it in the code, I would query all three classes then eliminate the duplicates while keeping the matches.

Query: Get all users whose name contains "William", category is "Single" and alias is "Bill". 

class ModelA(models.Model):
    user = models.ForeignKey(User,related_name="%(class)s",null=False)
    name = models.CharField(max_length=70)

    def __unicode__(self):
        return u'%s' % (self.name)

class ModelB(models.Model):
    user = models.ForeignKey(User,related_name="%(class)s",null=False)
    category = models.CharField(max_length=70)

    def __unicode__(self):
        return u'%s' % (self.category)

class ModelC(models.Model):
    user = models.ForeignKey(User,related_name="%(class)s",null=False)
    alias = models.CharField(max_length=70)

    def __unicode__(self):
        return u'%s' % (self.alias)

Note: this is an example only, and I am not looking to combine all the info in a single table.

When you say "trying to avoid joining the three tables in my code", do you mean explicitly (eg by writing raw SQL) or at all? Because it sounds like you're heading towards a solution that will have joins; but it should be possible to do it via the ORM. — James Aylett, Mar 11 '12 at 01:44

score 3 · Accepted Answer · answered Mar 11 '12 at 04:59

3

You can't do this without joining the three tables.

User.objects.filter(modela__name=u'William', modelb__category=u'Single',
  modelc__alias=u'Bill')

answered Mar 11 '12 at 04:59

Ignacio Vazquez-Abrams

776,304
153
1,341
1,358

Should be, assuming everything you wrote was accurate. – Ignacio Vazquez-Abrams Mar 12 '12 at 05:03

Join tables in Django that share the same ForeignKey

1 Answers1