Re: SELECT * FROM `student` WHERE mark=(select max(mark) from student)
On Oct 26, 10:24 am, Phlipwrote: > > This sounds like what django-reversion[1] does :) > > > [1]:http://github.com/etianen/django-reversion#readme > > We have to cover the situation where some clients might still have > rev(n-1), while some are up-to-date with rev(n). So we _probably_ need > the history in the same table as the current version. > > I'm aware this is borderline "big requirements up front", but the > answer turns out to be... > > class ThingManager(models.Manager): > > def get_query_set(self): > qs = super(ThingManager, self).get_query_set() > max_pids = QuerySet(self.model, using=self._db) > max_pids = > max_pids.values('name').annotate(Max('pid')).values('pid') Uh, that was max_pids = max_pids.values('name').annotate(max_id=Max('pid')).values('pid') If you don't name it you don't get the Max, even though nobody uses the max_id in the resulting SELECT statement: SELECT "things".*, FROM "things" WHERE ("things"."pid" IN ( SELECT MAX(U0."pid") AS "max_id" FROM "things" U0 GROUP BY U0."name" )) > return qs.filter(pid__in=max_pids) > > Now we can write any ORM statement we can think of, and (if those > lines continue to pass tests) then we only see the top horizon of the > data. Unless we need to go deeper. > > Thanks, all! > > -- > Phlip -- 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.
Re: SELECT * FROM `student` WHERE mark=(select max(mark) from student)
> Things.objects.filter(id__in=Things.objects.values('name').annotate(max_id= > Max('id')).values_list('max_id', > flat=True)) I didn't do values_list because I guessed that the inner query would run and produce an array, then the outer query would run. My way, with values() on both sides of the aggregate(), MIGHT insert the inner SELECT statement into the outer one, like our SQL examples. -- 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.
Re: SELECT * FROM `student` WHERE mark=(select max(mark) from student)
On Tue, Oct 26, 2010 at 12:40 PM, Phlipwrote: > > So this statement correctly fetches only the latest items: > > SELECT a.* FROM things a WHERE a.pid in (select max(b.pid) from > content_entity b group by b.name) > > Now I thought (from my allegedly copious experience with SQL) that I > could do it with a join-on-self, but I can't seem to get the SQL > syntax right. And if I did, I would then not know how to ORM-ize that > syntax (and yes it must be ORM-ized, because this is indeed the core > of the project, and everything has to see top-level horizons. Except > auditors). > Regarding this query, I think you may be able to do this using annotate. See http://docs.djangoproject.com/en/dev/topics/db/aggregation/#values For example (and this probably sucks for performance): Things.objects.filter(id__in=Things.objects.values('name').annotate(max_id=Max('id')).values_list('max_id', flat=True)) This is just a self join example, but it could probably be rewritten to use the two tables in your example. -- 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.
Re: SELECT * FROM `student` WHERE mark=(select max(mark) from student)
> This sounds like what django-reversion[1] does :) > > [1]:http://github.com/etianen/django-reversion#readme We have to cover the situation where some clients might still have rev(n-1), while some are up-to-date with rev(n). So we _probably_ need the history in the same table as the current version. I'm aware this is borderline "big requirements up front", but the answer turns out to be... class ThingManager(models.Manager): def get_query_set(self): qs = super(ThingManager, self).get_query_set() max_pids = QuerySet(self.model, using=self._db) max_pids = max_pids.values('name').annotate(Max('pid')).values('pid') return qs.filter(pid__in=max_pids) Now we can write any ORM statement we can think of, and (if those lines continue to pass tests) then we only see the top horizon of the data. Unless we need to go deeper. Thanks, all! -- Phlip -- 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.
Re: SELECT * FROM `student` WHERE mark=(select max(mark) from student)
On 26 October 2010 19:05, Steve Holdenwrote: > On 10/26/2010 12:40 PM, Phlip wrote: >> Note that "isabelle_item" appears twice. We are following the auditing >> rule "always write new records to change data - never edit previous >> records". Someone edited isabelle_item's payload data (not shown), so >> we add a new record without touching the existing record. > > I hope the auditors are only forcing you to do this with records that > aren't referenced as part of relationships, otherwise your database is > going to get hammered updating all the foreign keys. > > Wouldn't it make more sense (not that auditors will necessarily be > persuaded by sensible arguments) to dump a copy of a row (plus possibly > a timestamp field) to an archival table before update? This coild easily > be done on a pre-save signal ... This sounds like what django-reversion[1] does :) [1]: http://github.com/etianen/django-reversion#readme -- Łukasz Rekucki -- 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.
Re: SELECT * FROM `student` WHERE mark=(select max(mark) from student)
> I hope the auditors are only forcing you to do this with records that > aren't referenced as part of relationships, otherwise your database is > going to get hammered updating all the foreign keys. The design spec (which is ours, not any "CPA auditor's"), say to duplicate the living crap out of them. Plz don't go there. > Wouldn't it make more sense (not that auditors will necessarily be > persuaded by sensible arguments) to dump a copy of a row (plus possibly > a timestamp field) to an archival table before update? This coild easily > be done on a pre-save signal ... Maybe. Now how do I do a "group by" on an aggregate? I'm down to this: st=Student.objects.filter(marks__in=Student.objects.all().aggregate(Max('marks'), group_by='name')) Guess what? The "group_by" doesn't work, and Googling for "group by" returns all kinds of useless newb crap. Just sayin... -- 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.
Re: SELECT * FROM `student` WHERE mark=(select max(mark) from student)
On 10/26/2010 12:40 PM, Phlip wrote: > Note that "isabelle_item" appears twice. We are following the auditing > rule "always write new records to change data - never edit previous > records". Someone edited isabelle_item's payload data (not shown), so > we add a new record without touching the existing record. I hope the auditors are only forcing you to do this with records that aren't referenced as part of relationships, otherwise your database is going to get hammered updating all the foreign keys. Wouldn't it make more sense (not that auditors will necessarily be persuaded by sensible arguments) to dump a copy of a row (plus possibly a timestamp field) to an archival table before update? This coild easily be done on a pre-save signal ... regards Steve -- DjangoCon US 2010 September 7-9 http://djangocon.us/ -- 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.
Re: SELECT * FROM `student` WHERE mark=(select max(mark) from student)
Tom Evans wrote: > Phlip, I'm going to try and make a non-stupid comment now :) http://xkcd.com/386/ > If you already know precisely the query you want to use, and you can't > coerce django's ORM to produce it, can you simply use Manager.raw()[1] > to generate the result set you are after? Because the point of an ORM is to distribute declarations of relations among objects, so each object adds details to a query set, and the ORM can build the final SELECT statements for each context. So anyway, here's a table (hand-censored - it's a blue-sky project in a hyper-competitive space): > select * from things; +-+-+ | pid | name| +-+-+ | 6 | soca_2k7_user | | 7 | isabelle_item | | 8 | max_item| | 9 | isabelle_item | +-+-+ Note that "isabelle_item" appears twice. We are following the auditing rule "always write new records to change data - never edit previous records". Someone edited isabelle_item's payload data (not shown), so we add a new record without touching the existing record. This implies that all normal database queries should only look at the "top level horizon" of the database. (And this implies we must mix-and- match such queries, and they can't all rely on the order_by('-pk')[0] trick.) So this statement correctly fetches only the latest items: SELECT a.* FROM things a WHERE a.pid in (select max(b.pid) from content_entity b group by b.name) Now I thought (from my allegedly copious experience with SQL) that I could do it with a join-on-self, but I can't seem to get the SQL syntax right. And if I did, I would then not know how to ORM-ize that syntax (and yes it must be ORM-ized, because this is indeed the core of the project, and everything has to see top-level horizons. Except auditors). -- Phlip http://zeekland.zeroplayer.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.
Re: SELECT * FROM `student` WHERE mark=(select max(mark) from student)
Phlip, I'm going to try and make a non-stupid comment now :) If you already know precisely the query you want to use, and you can't coerce django's ORM to produce it, can you simply use Manager.raw()[1] to generate the result set you are after? Eg, Student.objects.raw(r'SELECT * FROM `student` WHERE mark=(select max(mark) from student)') Cheers Tom [1] http://docs.djangoproject.com/en/1.2/topics/db/sql/ On Tue, Oct 26, 2010 at 3:17 PM, Phlipwrote: >> st=Student.objects.filter(marks__in=Student.objects.all().aggregate(Max('ma >> rks'))) > > Aha - a marks__in may point to an aggregate subquery. > > In conclusion, screw my SQL server's optimizer. It deserves to suffer! > > (I can't seem to find a self-join to do what I need either...) > > -- > 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.
Re: SELECT * FROM `student` WHERE mark=(select max(mark) from student)
> st=Student.objects.filter(marks__in=Student.objects.all().aggregate(Max('ma > rks'))) Aha - a marks__in may point to an aggregate subquery. In conclusion, screw my SQL server's optimizer. It deserves to suffer! (I can't seem to find a self-join to do what I need either...) -- 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.
Re: SELECT * FROM `student` WHERE mark=(select max(mark) from student)
On Oct 26, 2:42 am, Tom Evanswrote: > I'm curious, why can't I talk you into > Student.objects.all().order_by('-score')[0] ? > > It is clearly a superior query :/ > >> ( BTW please don't try to talk me out of it; I've been doing SQL since > >> 1989 and am fully aware of all the alternatives there. C-; ) Hmm. Maybe I ought to start another thread where I describe the actual problem, in nauseatingly elaborate detail. In this thread I just want to learn any sick notations available there. Other mails not processed yet. BRB! -- 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.
Re: SELECT * FROM `student` WHERE mark=(select max(mark) from student)
Course there could be, I'm being dense :/ On Tue, Oct 26, 2010 at 10:55 AM, Piotr Kilczukwrote: > > > 2010/10/26 Tom Evans >> >> I'm curious, why can't I talk you into >> Student.objects.all().order_by('-score')[0] ? >> >> It is clearly a superior query :/ > > This would select only one row; there can possibly be multiple students with > a top note. > > Am I right? :) > > Regards, > Piotr > > -- > 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.
Re: SELECT * FROM `student` WHERE mark=(select max(mark) from student)
2010/10/26 Tom Evans> I'm curious, why can't I talk you into > Student.objects.all().order_by('-score')[0] ? > > It is clearly a superior query :/ > This would select only one row; there can possibly be multiple students with a top note. Am I right? :) Regards, Piotr -- 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.
Re: SELECT * FROM `student` WHERE mark=(select max(mark) from student)
I'm curious, why can't I talk you into Student.objects.all().order_by('-score')[0] ? It is clearly a superior query :/ Cheers Tom On Tue, Oct 26, 2010 at 6:37 AM, ankit raiwrote: > say your model name is Student, and it has a field names as marks > > query set will be > > Student.objects.all().aggregate(Max('marks') > this will be give you max marks > > st=Student.objects.filter(marks__in=Student.objects.all().aggregate(Max('marks'))) > > --ankit > > > On Tue, Oct 26, 2010 at 9:28 AM, Phlip wrote: >> >> Does anyone have a QuerySet for that? >> >> ( BTW please don't try to talk me out of it; I've been doing SQL since >> 1989 and am fully aware of all the alternatives there. C-; ) >> >> -- >> 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. > -- 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.
Re: SELECT * FROM `student` WHERE mark=(select max(mark) from student)
say your model name is Student, and it has a field names as marks query set will be Student.objects.all().aggregate(Max('marks') this will be give you max marks st=Student.objects.filter(marks__in=Student.objects.all().aggregate(Max('marks'))) --ankit On Tue, Oct 26, 2010 at 9:28 AM, Phlipwrote: > Does anyone have a QuerySet for that? > > ( BTW please don't try to talk me out of it; I've been doing SQL since > 1989 and am fully aware of all the alternatives there. C-; ) > > -- > 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.
SELECT * FROM `student` WHERE mark=(select max(mark) from student)
Does anyone have a QuerySet for that? ( BTW please don't try to talk me out of it; I've been doing SQL since 1989 and am fully aware of all the alternatives there. C-; ) -- 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.