1

In Markus Winand's blog post Pagination done the Postgresql way the following appears on slide 22:

select * from <table> where topic = 1234 
and (date, id) < (prev_date, prev_id)
order by date desc, id desc
limit 10;

This uses a composite value (or "row value") in the WHERE clause, which in 2013 was apparently only supported by Postgres. With correct indexing this should be more efficient than the OFFSET/LIMIT anti-pattern.

Is there any way of performing this type of WHERE clause in a sqlalchemy filter? My searches so far have only found information about composite keys.

David
  • 306
  • 2
  • 17
  • The linked question is helpful, however it is primarily concerned with the `in` function. I don’t see a way to map that to operators like `>` or `<`. – David Apr 25 '18 at 21:24
  • 2
    `tuple_(t.c.a, t.c.b) < (1, 2)` or `tuple_(...) < tuple_(...)` depending on whether or not you're using literals etc. It's not that big a leap, when you understand how SQLA overrides operators. – Ilja Everilä Apr 26 '18 at 07:44

0 Answers0