CODEWITHSUNDEEP
pythonsqlalchemy
Tohid
Tohid

Reputation: 22613

SQLAlchemy filtering Children in one-to-many relationships

I have defined my models as:

class Row(Base):
    __tablename__ = "row"
    id = Column(Integer, primary_key=True)
    key = Column(String(32))
    value = Column(String(32))
    status = Column(Boolean, default=True)
    parent_id = Column(Integer, ForeignKey("table.id"))

class Table(Base):
    __tablename__ = "table"
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False, unique=True)
    rows=relationship("Row", cascade="all, delete-orphan")

to read a table from the db I can simply query Table and it loads all the rows owned by the table. But if I want to filter rows by 'status == True' it does not work. I know this is not a valid query but I want to do something like:

session.query(Table).filter(Table.name == name, Table.row.status == True).one()

As I was not able to make the above query work, I came up with a new solution to query table first without loading any rows, then use the Id to query Rows with filters and then assign the results to the Table object: 

table_res = session.query(Table).option(noload('rows')).filter(Table.name == 'test').one()
rows_res = session.query(Row).filter(Row.parent_id == 1, Row.status == True)

table_res.rows = rows_res

But I believe there has to be a better way to do this in one shot. Suggestions?

Upvotes: 1

Views: 2090

Answers (1)

Halvor Holsten Strand
Halvor Holsten Strand

Reputation: 20536

You could try this SQLAlchemy query:

from sqlalchemy.orm import contains_eager

result = session.query(Table)\
    .options(contains_eager(Table.rows))\
    .join(Row)\
    .filter(Table.name == 'abc', Row.status == True).one()

print(result)
print(result.rows)

Which leads to this SQL:

SELECT "row".id AS row_id, 
    "row"."key" AS row_key, 
    "row".value AS row_value, 
    "row".status AS row_status, 
    "row".parent_id AS row_parent_id, 
    "table".id AS table_id, 
    "table".name AS table_name
FROM "table" JOIN "row" ON "table".id = "row".parent_id
WHERE "table".name = ? 
    AND "row".status = 1

It does a join but also includes the contains_eager option to do it in one query. Otherwise the rows would be fetched on demand in a second query (you could specify this in the relationship as well, but this is one method of solving it).

Upvotes: 1

Related Questions