On Mon, 2011-05-09 at 23:56 -0700, Michel30 wrote:
> Hey all,
>
> I'm having trouble wrapping my head around querying multiple tables
> with relations between them.
> I'm writing an (cms-like) app that will use an existing mysql
> database. I modeled that, got my code up and running and am now in the
> process of trying to retrieve and submit data to it.
> Here is an example of my model:
>
> class Author(models.Model):
> authorid = models.IntegerField(primary_key=True,
> db_column='AuthorID')
> firstname = models.CharField(max_length=96,
> db_column='FirstName')
> lastname = models.CharField(max_length=96, db_column='LastName')
> ....
> class Meta:
> db_table = u'Author'
> def __unicode__(self):
> return self.middleinitials
>
> class Documentrevision(models.Model):
> docrevid = models.IntegerField(primary_key=True,
> db_column='DocRevID')
> documentid = models.IntegerField(db_column='DocumentID')
> submitterid = models.IntegerField(db_column='SubmitterID')
> ...
> class Meta:
> db_table = u'DocumentRevision'
> def __unicode__(self):
> return self.documenttitle
>
> Now, for every documentid I retrieve I want to find it's firstname and
> lastname. Authorid relates to submitterid in this case.
> Can someone help me on my way on how to accomplish this?
>
> Many thanks,
> Michel
>
How about defining foreign key relation instead of raw integer id
fields? Since, after all, they are related, aren't they?
Then you can use magnificent ORM to do lot of tricks for you - including
retrieving data you just asked. And submitting it. And keeping all that
together.
So instead of submitterid you do following:
submitter = models.ForeignKey(Author, db_column='SubmitterID')
Same for documentid. Then assuming Document - Author relation is
many-to-many you can setup that relation using through
(Documentrevision) option. And now you can quite easily fetch all
authors for each Document.
Note that you should think in terms of ORM. Which means that you have
objects that relate each other, Document, DocumentRevision and Author.
Document has many DocumentRevisions and many authors.
So in Document model you do something like:
authors = model.ManyToMany(Author, through='DocumentRevision')
And now it's like walk in the park:
for document in Document.objects.all():
for author in document.authors:
print author.firstname, author.lastname
--
Jani Tiainen
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
