CODEWITHSUNDEEP
pythonsqlalchemy
Jon E
Jon E

Reputation: 119

SQLAlchemy find last column values where all values of another column exist

So this is probably a simple question but given a table that looks like

A    |  B   |  C
2020 | 1    | apple
2020 | 1    | pear
2020 | 1    | banana
2020 | 2    | apple
2020 | 2    | pear
2020 | 3    | apple
2020 | 3    | banana

And I want the last A, B combo that has both apple and pear in C

How do I do have a command in sqlalchemy s.t. it would return (2020, 2)

So I have

from sqlalchemy import func
from sqlalchemy.sql import desc


result = session.\
    query(table.A, table.B).\
    order_by(desc(table.A)).\
    order_by(desc(table.B)).\
    groupby(table.A, table.B).\
    having(func.count() == 2).\
    first()

But I need the logic of having to instead parse through column C and find the first guy that has both apple and pear

Upvotes: 0

Views: 102

Answers (2)

van
van

Reputation: 76992

In case there is no uniqueness across the 3 columns (for example, there can be 2 rows with (2020, 2, 'pear')), the below will still provide the solution which also works with any number of criterias:

c_list = ["apple", "pear"]
subq_list = [
    session.query(table.A, table.B)
    .filter(table.C == c_item)
    .distinct()
    .subquery()
    for c_item in c_list
]

# compose the query from the parts
s1, *s_rest = subq_list
stmt = select(s1).order_by(s1.c.A.desc(), s1.c.B.desc()).limit(1)
for s2 in s_rest:
    stmt = stmt.join(s2, and_(s1.c.A == s2.c.A, s1.c.B == s2.c.B))

for x in session.execute(stmt):
    print(x)

Upvotes: 1

Jon E
Jon E

Reputation: 119

Wow sorry, I figured it out really quickly after

from sqlalchemy import func
from sqlalchemy.sql import desc

c_list = ['apple', 'pear']

result = session.\
    query(table.A, table.B).\
    filter(table.C.in_(c_list)).\
    order_by(desc(table.A)).\
    order_by(desc(table.B)).\
    groupby(table.A, table.B).\
    having(func.count() == len(c_list)).\
    first()

The only problem is that, this assumes that all combinations of A, B, and C are distinct. Not sure how to get around that...

Upvotes: 0

Related Questions