No, what is an "ordering" table? The DAL assumes an id field as the primary key in all tables. Thus the internal ordering is always by this id field value. The natural ordering would then be by insertion order (later insertions would have a higher id value than earlier insertions). After some deletions and insertions even this ordering would get completely out of wack. Thus don't I need to provide the orderby argument in each and every select statement?
Outside of the DAL I guess I could add some Indices to the table for my primary keys. I'm not sure though how these get automagically used by the db when being accessed via the DAL. I mean, indices get used by a db if they are used in the WHERE statement of the select. The db (rows-selection-criteria) SQLSet creator function does NOT have the orderby argument - it is int the 'select' arguments. So I ahve no idea how thsi should work. On Jul 20, 12:14 pm, Yarko Tymciurak <yark...@gmail.com> wrote: > On Mon, Jul 20, 2009 at 2:07 PM, rb <rbspg...@gmail.com> wrote: > > > Oops. I made a small mistake. Isn't it true that I could get the Next > > record (of a table having the 'name' field as its primary key) using > > > rows = db(db.table.name > current_value).select(db.table.ALL, > > orderby=db.table.name, limitby=(0,1)) > > > Then my next record would be found in rows[0] ? > > Yes... btw, have you considered having an ordering table of your own (no > need to load 50K records)? > > > > > And to get the last record could I use: > > > rows = db().select(db.table.ALL, orderby=~db.table.name, limitby= > > (0,1)) > > > with the last record being in rows[0] ? > > > On Jul 20, 11:57 am, rb <rbspg...@gmail.com> wrote: > > > I am a bit flummoxed over dbs that insist on using an "id" field as > > > their primary key. Specifically I would like to be able to access > > > records via a more "btrieve" or old-style "ordered set" mode of > > > GetFirst, Get Prev, GetNext, GetLast. The web2py manual gives a neat > > > trick to paginate records which, unfortunately, depends upon the > > > orderby of the access being the same as the "id" primary key sorting > > > of the records. > > > > That is, you can divide up the records into pages (of say, 20 records > > > per page) and then select a page using the limitby(record-id-offset, > > > record-count) argument of the 'select' SQLSet function. I could use > > > this trick to make my GetFirst, GetPrev, GetNext, GetLast record > > > accesses ***if*** my record order were the same as the id-order, which > > > would not be true after the first record-delete then record-insert. > > > Because these two orders are not the same I need to first create a > > > record set which is ordered according to my primary key (e.g. 'name' > > > field) and then apply the above trick. However, in the DAL, the way > > > that orderby is effected is in the 'select' - thus I would need to > > > select the entire table of records, order them correctly in this > > > select, then (somehow) do a pagination on this record set. Obviously > > > if I have 50000 records and I want the next record, it makes no sense > > > to load 50k records to get that next one. > > > > Can anyone help me understand this better? What am I missing? Is there > > > a trick to keep my table ordered according to my key fieldS (I often > > > need more than one field in my key) in sync with the record's id ? (so > > > that I can just use the above noted trick to get the next record). --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "web2py-users" group. To post to this group, send email to web2py@googlegroups.com To unsubscribe from this group, send email to web2py+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/web2py?hl=en -~----------~----~----~----~------~----~------~--~---
