CODEWITHSUNDEEP
pythonsqlalchemy
A-Palgy
A-Palgy

Reputation: 1429

Sqlalchemy - how to get an object, and filter which rows in the child class

I’m new to Sqlalchemy, and in need of some help.

i have a model, with a one to many relation:

class Metnadev(DeclarativeBase):
    __tablename__ = 'metnadev'

    #personal info
    id = Column(Integer, autoincrement=True, primary_key=True)
    first_name = Column(Unicode(255))
    last_name = Column(Unicode(255))
    birth_day = Column(Date)
    activitys = relationship("Activity", backref="metnadev")

class Activity(DeclarativeBase):
    __tablename__ = 'activity'

    id = Column(Integer, autoincrement=True, primary_key=True)
    metnadev_id = Column(Integer, ForeignKey('metnadev.id'))

    location = Column(Unicode(255))
    visible = Column(Boolean,default=True)

When I do

metnadev = DBSession.query(Metnadev).filter_by(id=kw['id']).one()

I get the object, with the child, great. I want to get the object, but only get the rows from the child class, where visible == True

I searched but I’m not sure how to do it, Thanks for the help

Upvotes: 2

Views: 2201

Answers (2)

jd.
jd.

Reputation: 10948

This section of the documentation has your answer: http://docs.sqlalchemy.org/en/rel_0_6/orm/relationships.html#building-query-enabled-properties

class Metnadev(DeclarativeBase):
    #...
    @property
    def activities(self):
        return object_session(self).query(Activity).filter_by(visible=True).with_parent(self).all()

Upvotes: 2

Rachel Sanders
Rachel Sanders

Reputation: 5874

There's a couple ways you can do this.

For a one-off, you can just run a second query:

from sqlalchemy import and_

activities = Activity.query.filter(and_(Activity.metnadev_id == kw['id'], Activity.visible==True)).all()

You can change the relationship so only visible items are returned:

activities = relationship("Activity",
               primaryjoin="and_(Activity.metnadev_id==Metnadev.id, "
               "Activity.visible==True)")

If you need more control you can join the tables, but it sounds like the relationship configuration would work for you. Let me know if that's not the case.

Hope that helps!

Upvotes: 2

Related Questions