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?

Daniel Roseman · Accepted Answer · 2010-07-26 15:30:45Z

2

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.

answered Jul 26, 2010 at 15:30

Daniel Roseman

602k68 gold badges910 silver badges923 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mike Shultz Over a year ago

However, if I try and jump that relationship with something like this: sigs[0].sigcategory.category, I just end up with this exception: 'RelatedManager' object has no attribute 'category'

Wolph Over a year ago

That's because there can be many sigcategories. It's not an instance, it's a manager. Try sigs[0].sigcategory.all()[0].category instead.

Mike Shultz Over a year ago

I can't believe that escaped me. Thanks guys.

Collectives™ on Stack Overflow

Pulling Data From Multiple Tables in Django

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related