mapping to arbitrary selectable fails

[Former user]

The attackment is to reproduce the error, the assertion at the end of the file would fail!

[Former user]

to reproduce the bug

[Mike Bayer]

this issue is an entirely valid question and i understand the confusion ! I apologize for the "invalid" label...trac wont let me change it to the more accurate "notabug" label (nothing is really "invalid".....).

youve got one attribute of the User class dependent on a database-calculated value. therefore the object has to be retrieved from the database to do so, which wont happen in this case because the instance is already present in the session. expiring the user allows it to work:

session.flush()

session.expire(user1)

user = session.query(User).selectone_by(name='user1')
assert user.post_count==2, 'user.post_count is %s'%user.post_count

see also:

http://www.sqlalchemy.org/docs/unitofwork.myt#unitofwork_api_refreshexpire

[Former user]

I'm still confused.

Did you mean the query session.query(User).selectone_by(name='user1') didn't really hit the database bacause there was already a user1 in the session's identity map ? So the result is simply the old instance in identity map? And the identity map behaves like cache ?

But since i saw the sql generated when i set engine.echo=True, i supposed that the query did hit the database, but sqlalchemy found this instance was already in the identity map, so simply ignored this query result, and make this query completely useless, is that right?

Then I suggest that use the result of latter query to update the old instance in identity map, rather than make a completely useless query.

Hope my pool english has described it clearly ;-) what's your opinion ?

[Mike Bayer]

no, this is the behavior of the session. the query is issued in all cases unless you do a straight get(id) for a value that is already in the session. since you are doing a select_by(), the session has no idea how that criterion, after translated into a SQL statement and issued to the database, results into what identities the database would return, and its only when it gets the rows back from the database that it sees, "oh I already have this ID locally". so its not useless SQL by any means. SA does not have a generic SQL caching system built in, the session only knows about primary key identity.

this behavior is fully described at:

http://www.sqlalchemy.org/docs/unitofwork.myt#unitofwork_identitymap

ways around this behavior include refresh(), expire(), load(), clear() described in the session chapter, as well as the more severe always_refresh argument on mapper described in the "advanced datamapping" chapter.