CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
user1960118
user1960118

Reputation: 369

Python SQLAlchemy get matching row and next row where column is the same

I have a table which looks like this:

+----------+----------+-------------+
| id       | name     | date        |   
+----------+----------+-------------+
| 1        | John     | 2018-08-21  |
+----------+----------+-------------+
| 2        | James    | 2018-08-22  |
+----------+----------+-------------+
| 3        | John     | 2018-08-22  |
+----------+----------+-------------+
| 4        | Paul     | 2018-08-23  |
+----------+----------+-------------+

I'm looking for a query that, when you select id = 1 it would return:

+----------+----------+-------------+
| id       | name     | date        |   
+----------+----------+-------------+
| 1        | John     | 2018-08-21  |
+----------+----------+-------------+
| 3        | John     | 2018-08-22  |
+----------+----------+-------------+

but when you select id = 2 you would get:

+----------+----------+-------------+
| id       | name     | date        |   
+----------+----------+-------------+
| 2        | James    | 2018-08-22  |
+----------+----------+-------------+

So in other words, the query should get return the matching row by id and the next row where the name is the same.

I would I accomplish this in sqlalchemy. All help appreciated.

Upvotes: 0

Views: 2326

Answers (2)

9000
9000

Reputation: 40884

To work with a relational database is to understand that there is no next row, no preset order. You need to request a particular order by sorting, then fetch as many records in that order as you need.

In this case, you use e.g. .filter(the_table.c.name == "John"), then .order_by("id", "name"), then .limit(2). If you fetched two rows there were two records that match your constraint. There can be no records, or just one record.

Things become trickier if you have multiple sets of records for John, and want a particular subsequence. For that you might add a condition like the_table.c.id >= certain_id, or something similar for the date.

In general, your data model is lacking an explicit way to link several records into one sequence. You don't have a "sequence id", or "previous record id", or something like that which would help see several records as related. Maybe you can use the timestamp column for that, if it fits your problem; it quite likely does not.

Upvotes: 3

mad_
mad_

Reputation: 8273

It would have been much easier to provide answer after seeing your attempt. Below is code snippet which might help you to understand 

subquery = session.query(Person.name.label('name')).subquery()
main_query = session.query(Person).join(subquery, Person.name==subquery.c.name).filter(Person.id==2).\
order_by(Person.id).limit(2)

The much cleaner approach would involve querying two times

get_name = session.query(Person).filter(Person.id==1).first()[0]
get_all_rows =session.query(Person).filter(Person.name==get_name).order_by(Person.id).limit(2)

Upvotes: 1

Related Questions