Pulling Data From Multiple Tables in Django

Question

I'm kind of new to Django and am having some trouble pulling from existing tables. I'm trying to pull data from columns on multiple joined tables. I did find a solution, but it feels a bit like cheating and am wondering if my method below is considered proper or not.

class Sig(models.Model):
        sig_id = models.IntegerField(primary_key=True)
        parent = models.ForeignKey('self')
        state = models.CharField(max_length=2, db_column='state')
        release_id = models.SmallIntegerField(choices=releaseChoices)
        name = models.CharField(max_length=255)
        address = models.CharField(max_length=255, blank=True)
        city = models.CharField(max_length=255, blank=True)
        zip = models.CharField(max_length=10, blank=True)
        phone1 = models.CharField(max_length=255, blank=True)
        fax = models.CharField(max_length=255, blank=True)
        email = models.EmailField(max_length=255, blank=True)
        url = models.URLField(max_length=255, blank=True)
        description = models.TextField(blank=True)
        contactname = models.CharField(max_length=255, blank=True)
        phone2 = models.CharField(max_length=255, blank=True)
        ratinggroup = models.BooleanField()
        state_id = models.ForeignKey(State, db_column='state_id')
        usesigrating = models.BooleanField()
        major = models.BooleanField()
        class Meta:
            db_table = u'sig'

    class SigCategory(models.Model):
        sig_category_id = models.IntegerField(primary_key=True)
        sig = models.ForeignKey(Sig, related_name='sigcategory')
        category = models.ForeignKey(Category)
        class Meta:
            db_table = u'sig_category'

    class Category(models.Model):
        category_id = models.SmallIntegerField(primary_key=True)
        name = models.CharField(max_length=255)
        release_id = models.SmallIntegerField()
        class Meta:
            db_table = u'category'

Then, this was my solution, which works, but doesn't quite feel right:

sigs = Sig.objects.only('sig_id', 'name').extra(
            select = {
                'category': 'category.name',
            },
        ).filter(
            sigcategory__category__category_id = categoryId,
            state_id = stateId
        ).order_by('sigcategory__category__name', 'name')

Now since the items in filter() join the sigcategory and category models, I was able to pull category.name out by using extra(). Is this a proper way of doing this? What if I did not have the reference in filter() and the join did not take place?

Answer 1

SigCategory has a ForeignKey pointing at Category, so you can always get from the SigCategory to the Category simply by doing mysigcategory.category (where mysigcategory is your instance of SigCategory.

If you haven't previously accessed that relationship from that instance, doing it here will cause an extra database lookup - if you're concerned about db efficiency, look into select_related.