4 ms·
The Use The Index Luke material about this is essential background reading, but in terms of actually implementing: My favorite SQL implementation of cursor-bas
by dmitryminkovsky 6y ago
The Use The Index Luke material about this is essential background reading, but in terms of actually implementing:
My favorite SQL implementation of cursor-based keyset pagination can be found on this Hasura issue: https://github.com/hasura/graphql-engine/issues/141 https://github.com/hasura/graphql-engine/issues/141, specifically this comment: https://github.com/hasura/graphql-engine/issues/141#issuecomment-406006229 https://github.com/hasura/graphql-engine/issues/141#issuecom....
Something so elegant about this SQL. I enjoy it a lot.
Also really enjoy this answer about how to implement keyset pagination on multiple columns:
https://stackoverflow.com/questions/38017054/mysql-cursor-based-pagination-with-multiple-columns/38017813 https://stackoverflow.com/questions/38017054/mysql-cursor-ba...
- altdatathrow 6y agofyi that mysql multiple column answer is incorrect, mysql does indeed support row/tuple comparisons [1]: > For row comparisons, (a, b) > (x, y) is equivalent to: > (a > x) OR ((a = x) AND (b > y)) [1] https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html https://dev.mysql.com/doc/refman/8.0/en/comparison-operators...
- djeiasbsbo 6y agoJust in case you might be able to help me out.. is there any SQL flavor that supports specifying any except certain columns? For example something like: SELECT * EXCEPT FOO_ID FROM FOO; I have always wanted this but have never vome across it...
- rossmohax 6y agoMaybe WHERE NOT EXIST (SELECT ...) can help?
- boudin 6y agoI think the question was about excluding columns, not rows. I don't know any way to do that personally.
- dmitryminkovsky 6y agoNever heard of such a thing but maybe a good question for SO.
- bmn__ 6y agoThe correct answer is to just apply a view and enumerate the wanted column names once. Otherwise, dynamic column names (that's your search engine fodder) require introspection… -- "Chinook" sample database select column_name from information_schema.columns where table_name = 'Track' except select 'UnitPrice' … coupled with the moral equivalent of PL/pgSQL `execute` (i.e. run-time eval) statement.
- djeiasbsbo 6y agoThanks for answering! Currently I do use views but it's just something I had wondered about. I mean, the information from the information_schema must be updated anyway when one deletes a column or table, so I thought maybe a function like that which looks it up could exist. I will try with PL/pgSQL, have long wanted to familiarise myself with it anyway.
- lkbm 6y agoI've also wanted this on Postgres for years. If there's subset of rows you frequently want, you may just be able to define a view and use that. (At one point, I defined a text macro in my terminal to list the fields I usually wanted on our "orders" table.)
- atombender 6y agoPostgres has a clever trick [1] you can use, though I wouldn't say it's something I would ever use. [1] https://blog.jooq.org/2018/05/14/selecting-all-columns-except-one-in-postgresql/ https://blog.jooq.org/2018/05/14/selecting-all-columns-excep...
- dmitryminkovsky 6y agoThat is good to know! I’ve only used this approach with PGSQL
- djrobstep 6y agoThat multi column answer doesn't seem to address pagination of queries where some of the columns are in descending order and some aren't. I implemented a keyset paging library for sqlalchemy which supports this, and found the nicest way to do it is to swap the comparisons for the descending columns. For instance if this is an all-ascending paginated query: select ... where row(c1, c2, c3) > row(11, 12, 13) order by c1, c2, c3 Then with c2 descending it would look like this: select ... where row(c1, 12, c3) > row(11, c2, 13) order by c1, c2 desc, c3