Re: creating models / performance issue
that works (to my surprise). thanks a lot. Am 04.10.2006 um 18:49 schrieb DavidA: > > > va:patrick.kranzlmueller wrote: >> I have a model with blog-postings, assigned to a specific user - the >> user is stored in the table "posting" via foreignkey. >> >> the user is part of a grade, part of a school and part of a state - >> all of which have overview-pages. >> so, to get all the postings for a state I have to do the following: >> >> 1. search for schools in that state >> 2. search for grades in that school >> 3. search for users within that grade >> 4. and finally, get all the postings for that user >> >> seems a little complicated. > > Not sure if I understand your models exactly, but you don't need to do > four "searches" (queries), you just need to do one query with the > constraint that spans the four relations. Assuming your models look > something like this: > > class State(models.Model): > code = models.CharField(maxlength=2) > > class School(models.Model): > state = models.ForeignKey(State) > > class Grade(models.Model): > school = models.ForeignKey(School) > > class User(models.Model): > grade = models.ForeignKey(Grade) > > class Posting(models.Model): > user = models.ForeignKey(User) > > Then you can just do this to find all postings for users in grades in > schools in New York: > >Posting.objects.filter(user__grade__school__state__code='NY') > > Which will translate into one query looking something like this: > > select * from posting > join user on posting.user_id = user.id > join grade on user.grade_id = grade.id > join school on grade.school_id = school.id > join state on school.state_id = state.id > where state.code = 'NY' > > (abbreviated - the actual Django version of the SQL will be quite a > bit > more verbose due to fully qualified column names). > > Of course, I may have completely misunderstood your models and this > may > be completely off base! > -Dave > > > > --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-users -~--~~~~--~~--~--~---
Re: creating models / performance issue
va:patrick.kranzlmueller wrote: > I have a model with blog-postings, assigned to a specific user - the > user is stored in the table "posting" via foreignkey. > > the user is part of a grade, part of a school and part of a state - > all of which have overview-pages. > so, to get all the postings for a state I have to do the following: > > 1. search for schools in that state > 2. search for grades in that school > 3. search for users within that grade > 4. and finally, get all the postings for that user > > seems a little complicated. Not sure if I understand your models exactly, but you don't need to do four "searches" (queries), you just need to do one query with the constraint that spans the four relations. Assuming your models look something like this: class State(models.Model): code = models.CharField(maxlength=2) class School(models.Model): state = models.ForeignKey(State) class Grade(models.Model): school = models.ForeignKey(School) class User(models.Model): grade = models.ForeignKey(Grade) class Posting(models.Model): user = models.ForeignKey(User) Then you can just do this to find all postings for users in grades in schools in New York: Posting.objects.filter(user__grade__school__state__code='NY') Which will translate into one query looking something like this: select * from posting join user on posting.user_id = user.id join grade on user.grade_id = grade.id join school on grade.school_id = school.id join state on school.state_id = state.id where state.code = 'NY' (abbreviated - the actual Django version of the SQL will be quite a bit more verbose due to fully qualified column names). Of course, I may have completely misunderstood your models and this may be completely off base! -Dave --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-users -~--~~~~--~~--~--~---
Re: creating models / performance issue
> so, to get all the postings for a state I have to do the following: > > 1. search for schools in that state > 2. search for grades in that school > 3. search for users within that grade > 4. and finally, get all the postings for that user > > seems a little complicated. Actually, this is what RDBMS's are good for. Django's Querysets should have no problems pulling user postings like this by traversing through all your tables (using appropriate SQL JOINs). If you have already tried this and are having performance issues, consider adding INDEXES on fields that were used in the Queryset composition. You can do this in your models using db_index=True on relevant fields of each model. For example, add an index on the state field of School, school field of Grade, and so on (assuming School, Grade are your models/tables). --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-users -~--~~~~--~~--~--~---
creating models / performance issue
I have a model with blog-postings, assigned to a specific user - the user is stored in the table "posting" via foreignkey. the user is part of a grade, part of a school and part of a state - all of which have overview-pages. so, to get all the postings for a state I have to do the following: 1. search for schools in that state 2. search for grades in that school 3. search for users within that grade 4. and finally, get all the postings for that user seems a little complicated. another way is to store grade, school and state in the postings- table. with that, the postings are a lot easier to retrieve. on the other hand, it seems a bit strange to store things twice (e.g. the user is assigned to a grade, so it´s kind of senseless to store the grade for a posting also). maybe someone can give me advice on which is the "better" solution. thanks, patrick --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-users -~--~~~~--~~--~--~---
