CODEWITHSUNDEEP
django
AP257
AP257

Reputation: 93973

Querying across database tables

I've got Django tables like the following (I've removed non-essential fields):

class Person(models.Model):
    nameidx = models.IntegerField(primary_key=True)
    name = models.CharField(max_length=300, verbose_name="Name")     
class Owner(models.Model):
    id = models.IntegerField(primary_key=True) 
    nameidx = models.IntegerField(null=True, blank=True) # is Person.nameidx
    structidx = models.IntegerField() # is PlaceRef.structidx
class PlaceRef(models.Model):
    id = models.IntegerField(primary_key=True) 
    structidx = models.IntegerField() # used for many things and not equivalent to placeidx
    placeidx = models.IntegerField(null=True, blank=True) # is Place.placeidx
class Place(models.Model):
    placeidx = models.IntegerField(primary_key=True) 
    county = models.CharField(max_length=36, null=True, blank=True)
    name = models.CharField(max_length=300)

My question is as follows. If in my views.py file, I have a Person referenced by name and I want to find out all the Places they own as a QuerySet, what should I do?

I can get this far:

person = Person.objects.get(name=name)
owned_relations = Owner.objects.filter(nameidx=nameidx)

How do I get from here to Place? Should I use database methods?

I'm also not sure if I should be using ForeignKey for e.g. Owner.nameidx.

Thanks and apologies for this extremely basic question. I'm not sure how to learn the basics of database queries except by trying, failing, asking SO, trying again... :)

Upvotes: 0

Views: 127

Answers (2)

Daniel Roseman
Daniel Roseman

Reputation: 600026

The whole point of foreign keys is for uses like yours. If you already know that Owner.nameidx refers to a Person, why not make it a foreign key (or a OneToOne field) to the Person table? Not only do you get the advantage of referential integrity - it makes it impossible to enter a value for nameidx that isn't a valid Person - the Django ORM will give you the ability to 'follow' the relationships easily:

owned_places = Place.objects.filter(placeref__owner__person=my_person)

will give you all the places owned by my_person.

Incidentally, you don't need to define the separate primary key fields - Django will do it for you, and make them autoincrement fields, which is almost always what you want.

Upvotes: 1

Rajeev
Rajeev

Reputation: 46979

If u could redesign.Then

 In owner nameidx can be a foreign key to Person(nameidx)
 Placeref(structidx) could be a foreign key to Owner(structidx) and 
 Place(placeidx) could be a foreign key Place ref(placeidx)

Then u could deduce the place value easily..

Upvotes: 0

Related Questions