Thanks for the explanation. I don't need a quick fix for my use-case. The problem only happens when writing tests and there I can just build a manual reload when I know the object is expired (I expire it manually on purpose).
On 08/09/2016 01:17 AM, Mike Bayer wrote: > > > On 08/08/2016 07:43 AM, Florian Rüchel wrote: >> I created a gist that recreates the issue when running SQLAlchemy >> 1.1.0b3: https://gist.github.com/Javex/41c58b098c1e5736cb2b21c4b6708be3 > > great test. So this is to do with the lazy loader which is not > expecting to see what it's seeing here. because your bound > parameter is part of a simple primary key comparison, it's getting > sucked into the lazyload SQL generation mechanics and multiple things > are going wrong with it from that point. > > I've summarized the issues here: > https://bitbucket.org/zzzeek/sqlalchemy/issues/3767/primaryjoin-w-user-defined-bind-parameter > > unfortunately I don't know that there's much of a workaround now > except perhaps to make a special subclass of BindParameter that tries > to hide here, but that might require a lot of hacky stuff to get it to > "hide" at the right time and not when it actually needs to be used. > > >> >> Traceback (most recent call last): >> File "broken_autojoin.py", line 73, in <module> >> print('Title: %s' %article.german.title) >> File >> "/home/javex/.virtualenvs/mtc3/lib/python3.5/site-packages/sqlalchemy/orm/attributes.py", >> >> line 237, in __get__ >> return self.impl.get(instance_state(instance), dict_) >> File >> "/home/javex/.virtualenvs/mtc3/lib/python3.5/site-packages/sqlalchemy/orm/attributes.py", >> >> line 584, in get >> value = self.callable_(state, passive) >> File >> "/home/javex/.virtualenvs/mtc3/lib/python3.5/site-packages/sqlalchemy/orm/strategies.py", >> >> line 553, in _load_for_state >> passive >> File >> "/home/javex/.virtualenvs/mtc3/lib/python3.5/site-packages/sqlalchemy/orm/strategies.py", >> >> line 589, in _get_ident_for_use_get >> for pk in self.mapper.primary_key >> File >> "/home/javex/.virtualenvs/mtc3/lib/python3.5/site-packages/sqlalchemy/orm/strategies.py", >> >> line 589, in <listcomp> >> for pk in self.mapper.primary_key >> KeyError: Column('language_id', Integer(), ForeignKey('language.id'), >> table=<article_i18n>, primary_key=True, nullable=False) >> >> Let me know if this breaks for you as well. If this is in fact a bug >> (and not some stupid mistake I made) and you happen to create an issue / >> fix for this I'd also be interested in seeing how it was fixed and where >> the problem was, so please link it here if possible so I can follow >> it :) >> >> Cheers, >> Florian >> >> On Sunday, 7 August 2016 23:58:04 UTC+10, Mike Bayer wrote: >> >> >> >> On 08/06/2016 08:03 PM, Florian Rüchel wrote: >> > Following up on this. I have implemented it in my application >> and it >> > works beautifully when querying for the object. >> > >> > However, while writing tests, I discovered that if the object is >> > expired, it doesn't know how to refresh it. To explain the issue, >> I need >> > to expand my original (simplified) example. The MyObjI18N class >> doesn't >> > store the locale directly. Instead it has another foreignkey to a >> > Language table. So my query in the above case looks actually like >> this: >> > >> > and_(MyObj.id == MyObjI18N.obj_id, request.language_id == >> Language.id) >> > >> > Now the problem is this: It doesn't seem to remember this >> language_id >> > when refreshing, leading to the following exception: >> > >> > .../python3.5/site-packages/sqlalchemy/orm/attributes.py:237: in >> __get__ >> > return self.impl.get(instance_state(instance), dict_) >> > .../python3.5/site-packages/sqlalchemy/orm/attributes.py:584: >> in get >> > value = self.callable_(state, passive) >> > .../python3.5/site-packages/sqlalchemy/orm/strategies.py:553: in >> > _load_for_state >> > passive >> > .../python3.5/site-packages/sqlalchemy/orm/strategies.py:589: in >> > _get_ident_for_use_get >> > for pk in self.mapper.primary_key >> > _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ >> _ _ _ >> > _ _ _ >> > >> > .0 = <tuple_iterator object at 0x7fe545dc97b8> >> > >> > get_attr( >> > state, >> > dict_, >> > self._equated_columns[pk], >> > passive=passive) >> >> for pk in self.mapper.primary_key >> > ] >> > E KeyError: Column('language_id', Integer(), >> > ForeignKey('language.id <http://language.id>'), >> table=<challenge_i18n>, primary_key=True, >> > nullable=False) >> > >> > .../python3.5/site-packages/sqlalchemy/orm/strategies.py:589: >> KeyError >> > >> > I dug into the problem a bit but this goes way more into the >> core of >> > SQLAlchemy than I am able to understand. In the end it seemed to >> be that >> > on the join condition the lazy clause created didn't contain the >> equated >> > column for language_id. But here my understanding of the working >> > internals comes to an end. >> > >> > I would have assumed that the instance somehow 'remembered' that >> column >> > from when it was loaded and would refresh it in the same way as it >> was >> > loaded. And from the setup, I feel like it should be possible. My >> best >> > guess would be that the primaryjoin I return doesn't help it >> > 'understand' that it has that link to a language_id column. >> >> I can't see how that would happen unless we're talking about >> something >> like clear_mappers(). the construction of SQL expressions and their >> linkages to mappers do not change with "expiration", and the recipe >> uses >> a bindparameter() object that similarly doesn't change. >> >> I can offer no insight here without complete specifics. >> >> >> >> > >> > FWIW I am running SQLAlchemy 1.1.0b3, in case this is actually >> > unexpected behaviour. >> > >> > Cheers, >> > Florian >> > >> > On Friday, 5 August 2016 00:24:30 UTC+10, Mike Bayer wrote: >> > >> > >> > >> > On 08/04/2016 10:14 AM, Florian Rüchel wrote: >> > > I have a relationship that depends on a query time >> variable to >> > determine >> > > the correct join. The use case is request-time localization >> in a web >> > > application. When running the query during a request, I >> want to >> > > determine the locale and only load the translation for the >> current >> > > language for a given object. However, the primaryjoin >> condition >> > callable >> > > is evaluated at mapping time instead which only happens once >> > instead of >> > > on every request. >> > > >> > > Here is a quick sample: >> > > >> > > def get_myobj_primaryjoin(): >> > > return and_(MyObj.id == MyObjI18N.obj_id, >> request.locale == >> > > MyObjI18N.lang) >> > > >> > > >> > > class MyObj(Base): >> > > id = Column(Integer, primary_key=True) >> > > _current_translation = relationship(MyObjI18N, >> uselist=False, >> > > primaryjoin=get_myobj_primaryjoin, lazy='joined') >> > > >> > > >> > > class MyObjI18N(Base): >> > > obj_id = Column(ForeignKey(MyObj.id), primary_key=True) >> > > lang = Column(String) >> > > >> > > This should give a rough idea of the issue: request.locale >> changes at >> > > query time, that is, if I do MyObj.query in two different >> > requests, it >> > > won't work, it will always take the first time it was >> called. >> > > >> > > Note that I was previously using a with_transformation >> approach when >> > > building the query but I wanted to remove the necessity to >> add that >> > > every time a build a query and would have it much rather >> built >> > implicitly. >> > > >> > > Any ideas are highly appreciated, no argument I can pass to >> > > "relationship" seems to help my use case. >> > >> > >> > we use a bound parameter for this and a recipe for getting a >> value in >> > there can be seen at >> > >> >> https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/GlobalFilter >> >> <https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/GlobalFilter> >> >> > >> >> <https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/GlobalFilter >> >> <https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/GlobalFilter>> >> >> > . >> > In particular the lazyload case can only be affected using >> a custom >> > MapperOption as described near the bottom of that recipe. >> > >> > >> > >> > > >> > > -- >> > > You received this message because you are subscribed to the >> Google >> > > Groups "sqlalchemy" group. >> > > To unsubscribe from this group and stop receiving emails >> from it, >> > send >> > > an email to sqlalchemy+...@googlegroups.com <javascript:> >> > > <mailto:sqlalchemy+unsubscr...@googlegroups.com >> <javascript:> <javascript:>>. >> > > To post to this group, send email to >> sqlal...@googlegroups.com >> > <javascript:> >> > > <mailto:sqlal...@googlegroups.com <javascript:>>. >> > > Visit this group at >> https://groups.google.com/group/sqlalchemy >> <https://groups.google.com/group/sqlalchemy> >> > <https://groups.google.com/group/sqlalchemy >> <https://groups.google.com/group/sqlalchemy>>. >> > > For more options, visit https://groups.google.com/d/optout >> <https://groups.google.com/d/optout> >> > <https://groups.google.com/d/optout >> <https://groups.google.com/d/optout>>. >> > >> > -- >> > You received this message because you are subscribed to the Google >> > Groups "sqlalchemy" group. >> > To unsubscribe from this group and stop receiving emails from it, >> send >> > an email to sqlalchemy+...@googlegroups.com <javascript:> >> > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>. >> > To post to this group, send email to sqlal...@googlegroups.com >> <javascript:> >> > <mailto:sqlal...@googlegroups.com <javascript:>>. >> > Visit this group at https://groups.google.com/group/sqlalchemy >> <https://groups.google.com/group/sqlalchemy>. >> > For more options, visit https://groups.google.com/d/optout >> <https://groups.google.com/d/optout>. >> >> -- >> You received this message because you are subscribed to the Google >> Groups "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send >> an email to sqlalchemy+unsubscr...@googlegroups.com >> <mailto:sqlalchemy+unsubscr...@googlegroups.com>. >> To post to this group, send email to sqlalchemy@googlegroups.com >> <mailto:sqlalchemy@googlegroups.com>. >> Visit this group at https://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.