Hi All I'm new to SQLAlchemy (love it) and also new to SQL in general, although I have 25 years experience in a range of obscure databases. I have what I hope will be a simple question as I believe I'm missing a critical understanding of some of the underlying SQL machinery.
This is the situation (dumbed down for the example); 1) I have selected a row from a table "somewhere else" and therefore have a session.query resulted mapped class instance of that row (Or the primary key(s), which ever is most useful). We'll call this row "Fred". 2) I have an ordered_by "x" result of a query on the same table (with many rows) that I wish to navigate with first/next/prev/last type controls. This I have implemented using the cool result[position] syntax and that all works fine. (Gets a little slow over several million rows, but that's outside the scope of the design requirement) My problem is this; I want to find out what position my record"Fred" is within the larger result set.. so that I can then locate myself onto "fred" and move to the next or previous row in that query as normal. To do that, I need the "position" value so I can do the slice. Sure, I could do an .all() on the query and then loop through until I found "fred" counting as I go, but that's horrible and smacks of "you don't know what you are doing".. Picture it like this; table has rows 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. I query that and order it by something else which gives me an ordered result of 1, 3, 5, 7, 9, 2, 4, 6, 8, 0 I know that fred's key is "9", so I want to determine that in the ordered list (above), "9" is the 4th (zero based) element. I can then locate fred by saying result[4] and "next" is simply result[5] etc. Is there a good way? I suspect I could do something with a secondary query that would produce the results 1, 3, 5, 7 and then I could count that and that would be my position, but it's all sounding a little amateurish to me. Any advice would be most appreciated. :-) Cheers Warwick Warwick Prince Managing Director mobile: +61 411 026 992 skype: warwickprince phone: +61 7 3102 3730 fax: +61 7 3319 6734 web: www.mushroomsys.com -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
