CODEWITHSUNDEEP
sqlsqlalchemy
Ioan Kats
Ioan Kats

Reputation: 521

query list of users after specified id in sqlalchemy.Table

Letss say that we have 10000 users on this data table.

users = sqlalchemy.Table(
    "users",
    metadata,
    sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column("email", sqlalchemy.String),
    sqlalchemy.Column("registration_date", sqlalchemy.String),
)

If the table is ordered by registration_date and I have a user id, how can I query the next 2 users after that id.

e.g if this is the db table and I have the user id 1703, I want to get the next two users in the list.

3872    | [email protected]    | 2019-09-22T17:34:31.260Z
1703    |[email protected]       | 2019-09-21T02:28:58.914Z
6657    |[email protected]  | 2019-09-20T18:52:43.073Z
6689    |[email protected]    | 2019-09-20T00:16:39.334Z

I tried something like

query = users.select().where(users.c.id == 1703).limit(2)

but I get only the specified user as response

Upvotes: 0

Views: 67

Answers (1)

Ruben Helsloot
Ruben Helsloot

Reputation: 13139

I'd do something like the following SQL:

SELECT *
FROM users
WHERE users.registration_date > (SELECT registration_date FROM users WHERE id = 2)
ORDER BY users.registration_date
LIMIT 2

In SQLAlchemy, you can do that using a subquery

registration_date_min = select([users.c.registration_date])\
  .where(users.c.id == 2)\
  .as_scalar()
users.select()\
  .where(users.c.registration_date > registration_date_min)\
  .order_by(users.c.registration_date)\
  .limit(2)

If you want strict ordering (for example when two people have the exact same registration date), then I'd order by registration_date, id, and use

WHERE users.registration_date > (SELECT registration_date FROM users WHERE id = 2)
   OR (users.registration_date = (SELECT registration_date FROM users WHERE id = 2)
       AND users.id > 2)

Upvotes: 1

Related Questions