Defining a SQL view is possible but ugly. I don't believe MySQL supports a natural join, so I would have to name each (of 500) individual fields to deal with the id columns being identical in each table. Drawbacks are: 1- just plain ugly; 2- will not stay in sync with my model but must be updated manually; 3- so far I have not had to use any sql and would like to keep it that way.
I've only been using Django for a month and don't feel comfortable writing my own manager. What do you think about creating a model that has a foreign key to each of the other models and using a select related on it when I need them all? -----Original Message----- From: django-users@googlegroups.com [mailto:django-us...@googlegroups.com] On Behalf Of Steve Holden Sent: Saturday, July 17, 2010 9:53 AM To: django-users@googlegroups.com Subject: Re: Get all tables in one query that use OneToOne relationship On 7/16/2010 7:28 PM, Sells, Fred wrote: > I've got a logical record 0f 500 columns that is broken up into about 20 > tables based on an implicit logical grouping of the data. Most of the > time this works well and the code is clean; but there are a few use > cases where I need to get all the equivalent fields for a single record > from all the tables. The "select_related()" seems to only follow to the > "parent" table, while I want to use the parent table to get all the > related "child" (or sibling to be more precise) columns. I know I could > use a for loop and a __dict__.update() to make multiple queries of the > DB and get the data, or I could write some SQL to select all; but > neither seem very Pythonic. > > Is there a clean way to do this in Django? > Maybe you could define a view in SQL (using CREATE VIEW AS <select statement>), joining all the necessary tables, that Django could then treat as a (preferably read-only) model to give you access to the joined tables? If you are competent enough to write a special-purpose Manager to load the columns lazily when the code requested them. regards Steve > > > My models.py contains this code (snipped for brevity) > > class Assessment(models.Model): > facility = models.ForeignKey(Facility, default='MZ') > resid = models.CharField(max_length=7, default='MZ00001') > status = models.IntegerField(default=0) > > class MDSSection(models.Model): > assessment = models.OneToOneField(Assessment, primary_key=True) > > class Meta: > abstract= True > > class A(MDSSection): > A0100A = models.CharField(max_length=10, help_text='''Text : > Facility National Provider Identifier (NPI)''') > A0100B = models.CharField(max_length=12, help_text='''Text : > Facility CMS Certification Number (CCN)''') > A0100C = models.CharField(max_length=15, help_text='''Text : > State provider number''') > > class B(MDSSection): > B0100 = models.CharField(max_length= 1, help_text='''Code : > Comatose''') > B0200 = models.CharField(max_length= 1, help_text='''Code : > Hearing''') > B0300 = models.CharField(max_length= 1, help_text='''Code : > Hearing aid''') > > ... > -- Steve Holden +1 571 484 6266 +1 800 494 3119 DjangoCon US September 7-9, 2010 http://djangocon.us/ See Python Video! http://python.mirocommunity.org/ Holden Web LLC http://www.holdenweb.com/ -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-us...@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. -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-us...@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.
