CODEWITHSUNDEEP
pythonsqlpostgresqlsqlalchemy
melchoir55
melchoir55

Reputation: 7276

sqlalchemy filter children in query, but not parent

I have a query which returns a User object. Users have a number of Posts which they have made. When I perform a query, I want to filter the posts found in User.post based on a specific time. For example, to return only the posts within 1 day of a given timestamp.

class User(base):
    __tablename__ = 'User'

class Post(base):
    __tablename__ = 'Post'

    user_id = Column(Integer, ForeignKey('User.uid'))
    user = relationship(User, backref=backref('post')
    time = Column(DateTime, default=func.current_timestamp())

Is there a way to dynamically change the way the children are loaded by the orm, and to specify that at query time? Something like (pseudocode):

User.query.filter_child(Post.time in timespan)

Importantly, I don't want to filter out parents if there are no children which match the criteria. I only want to filter the children loaded by the orm for the given Users.

Upvotes: 3

Views: 3886

Answers (2)

Ilja Everilä
Ilja Everilä

Reputation: 52929

Loading custom filtered collections is done using contains_eager(). Since the goal is to load all User objects even if they have no Post objects that match the filtering criteria, an outerjoin() should be used and the predicates for posts put in the ON clause of the join:

end = datetime(2019, 4, 10, 12, 0)
start = end - timedelta(days=1)

# If you want to use half closed intervals, replace `between` with
# suitable relational comparisons.
User.query.\
    outerjoin(Post, and_(Post.user_id == User.uid,
                         Post.time.between(start, end))).\
    options(contains_eager(User.post))

Upvotes: 3

F. Pareto
F. Pareto

Reputation: 334

I'm not sure this is what you are looking for, but what's wrong with this KISS approach?

Post.query.filter(Post.user == <username>).filter(
    Post.time > START_OF_DAY).filter(Post.time < END_OF_DAY).all()

Upvotes: -1

Related Questions