CODEWITHSUNDEEP
pythonsqlalchemy
Kamil Sindi
Kamil Sindi

Reputation: 22822

Two sqlalchemy queries return the same result even though they have different filters

Let's say I have 2 classes: Foo and Bar. Assume that a Foo can have many Bars (via foo.Bars). Bar has an end_date to indicate when a bar has ended. Bars that have not ended are set to '9999-12-31' by default.

I've written two queries below to get a Foo but with different bar filters such that the first query gives me bars that have not ended; the second gives me bars that have ended.

   today = date.today().strftime("%Y-%m-%d")

   current = session.query(Foo)\
                        .join(Bar)\
                        .filter(
                            and_(
                                 Foo.code=='MY_STRING',
                                 Bar.end_date >= today,
                                 )
                        )\
                        .options(contains_eager('Bars'))\
                        .one()

   previous = session.query(Foo)\
                        .join(Bar)\
                        .filter(
                            and_(
                                 Foo.code=='MY_STRING',
                                 Bar.end_date < today,
                                 )
                        )\
                        .options(contains_eager('Bars'))\
                        .one()

For some reason previous.Bars are the same as current.Bars even though we've filtered the bars to be different (at least that was my attempt). E.g. current.Bars returns [bar1, bar2, bar3] and previous.Bars returns [bar1, bar2, bar3].

If I switch the two queries (i.e. put previous before current), then current's Bars are the same as previous.Bars. E.g. current.Bars returns [bar4, bar5, bar6] and previous.Bars returns [bar4, bar5, bar6].

Note that I'm 100% sure that the underlying data in previous.Bars should be different from current.Bars. I.e. I should get [bar4, bar5, bar6] for previous.Bars and [bar1, bar2, bar3] for current.Bars

Can someone please explain why I'm getting the same results? How can I solve this? I realize it's probably better just to have one query.

Thanks in advance!

PS I'm using version 0.8

Upvotes: 2

Views: 414

Answers (1)

van
van

Reputation: 76952

The problem line is .options(contains_eager('Bars')). It basically tells sqlachemy that that query contains all Bars for each foo loaded. So when you access foo.Bars, it does not query the database anymore, because it has taken those (filtered) bars from the join and assumed them to be a full foo.Bars relationship.
The next time you run the query, sqlalchemy realizes that the object Foo is already loaded (including its relationship Bars) and basically returns what it currently has in the session (memory).

You can work-around this by removing the object from the session using session.expunge(current) just before you execute query for previous, so that sqlalchemy will reload it completely, but you have to be careful using current instance after that because it is not part of the session anymore.

Better yet: since you need both sides anyway, just do this separation on python side easily:

today = datetime.datetime.today()#.strftime("%Y-%m-%d")
foo = (session.query(Foo)
        .options(joinedload(Foo.Bars))
        .filter(Foo.code=='MY_STRING')
        .one()
        )
prev_bars = [bar for bar in foo.Bars if bar.end_date < today]
curr_bars = [bar for bar in foo.Bars if bar.end_date >=today]
print foo
print prev_bars
print curr_bars

Upvotes: 2

Related Questions