On Sep 9, 2010, at 4:04 AM, Warwick Prince wrote:
> 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. :-)
This is a straight up common pagination issue, and I've seen it occur when you
paginate through a list, click on one of the entries, and you get a "detail"
page for the entry, which has "previous entry" / "next entry" links on it.
The "1 3 5 7" solution is how it works.
The key is to make further usage of the "order by", and also that the "order
by" you're using is unique - those columns can then identify "fred" within the
context of your sorting. If the "order by" is not unique you would have to
add additional columns to it until it is.
So suppose you sorted the rows by a column "created_date" and then "id". So
things are in date order, and items on the same date are sorted by id. Fred's
position in the list is then:
select count(*) from table where (created_date < fred.created_date or
(created_date=fred.created_date and id < fred.id))
the "previous" entry would be:
select * from table where (created_date < fred.created_date or
(created_date=fred.created_date and id < fred.id)) order by created_date desc,
id desc limit 1
Taking advantage of the ordering can also help with your slowness over millions
of rows issue. Using OFFSET or similar is just plain slow with large results
- the DB has to scan through the whole thing to get to where you're asking.
If you are displaying a page however and know the last row of the previous
page, you can SELECT where (created_date > fred.created_date or
(created_date=fred.created_date and id > fred.id)).
>
> 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.
--
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.
