> > however, I can't think of any reason public use of .subquery() or > .exists() would ever *want* eagerloaded options to take place since by > definition .subquery() and .exists() are never used to load objects. > So in 1.2 I'll propose to make this automatic >
Yep, I agree with this! Thanks, I'll use .enable_eagerloads(False) to fix it for now. On Tuesday, July 18, 2017 at 11:56:08 AM UTC-7, Mike Bayer wrote: > > On Tue, Jul 18, 2017 at 2:13 PM, <vin...@benchling.com <javascript:>> > wrote: > > Hello, I'm running into some difference in behavior for .exists() in > > SQLAlchemy 1.0.17 and 1.1.11, related to subqueryloads. > > > > > > class A(db.Model): > > __tablename__ = 'a' > > > > id = db.Column(db.Integer, primary_key=True) > > b_id = db.Column(db.Integer, db.ForeignKey('b.id'), nullable=False) > > b = db.relationship('B', lazy='subquery') > > > > > > class B(db.Model): > > __tablename__ = 'b' > > > > id = db.Column(db.Integer, primary_key=True) > > > > > > In SQLAlchemy 1.0.17, I can do all of the following: > > > > A.query.exists() # Note that A.b has lazy='subquery' > > A.query.options(subqueryload(A.b)).exists() > > > > # We usually use this in the context of: > > db.session.query(A.query.exists()).scalar() > > There is a bug here in add_column() / subqueryload added as > > https://bitbucket.org/zzzeek/sqlalchemy/issues/4033/dont-crash-for-add_columns-when. > > > > however if you go into your 1.0 version and instead do lazy="joined" > you'll see your EXISTS query rendering out the LEFT OUTER JOIN > entirely unnecessarily. > > Current API when you're turning the query into a subquery or exists is > to call enable_eagerloads(False): > > print s.query(A).enable_eagerloads(False).exists() > > > the method is documented as such: > > > https://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=enable_eagerloads#sqlalchemy.orm.query.Query.enable_eagerloads > > > however, I can't think of any reason public use of .subquery() or > .exists() would ever *want* eagerloaded options to take place since by > definition .subquery() and .exists() are never used to load objects. > So in 1.2 I'll propose to make this automatic, that's > > https://bitbucket.org/zzzeek/sqlalchemy/issues/4032/enable_eagerloads-false-automatically-for. > > > > > > > > > > > > In SQLALchemy 1.1.11, we run into the following error: > > A.query.exists() # Note that A.b has lazy='subquery' > > > > .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in exists(self) > > 3046 # .with_only_columns() after we have a core select() so > that > > 3047 # we get just "SELECT 1" without any entities. > > -> 3048 return sql.exists(self.add_columns('1').with_labels(). > > 3049 statement.with_only_columns([1])) > > 3050 > > > > .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in > statement(self) > > 465 """ > > 466 > > --> 467 stmt = self._compile_context(labels=self._with_labels).\ > > 468 statement > > 469 if self._params: > > > > .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in > > _compile_context(self, labels) > > 3363 > > 3364 for entity in self._entities: > > -> 3365 entity.setup_context(self, context) > > 3366 > > 3367 for rec in context.create_eager_joins: > > > > .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in > > setup_context(self, query, context) > > 3725 with_polymorphic=self._with_polymorphic, > > 3726 only_load_props=query._only_load_props, > > -> 3727 > > polymorphic_discriminator=self._polymorphic_discriminator) > > 3728 > > 3729 def __str__(self): > > > > .../lib/python2.7/site-packages/sqlalchemy/orm/loading.pyc in > > _setup_entity_query(context, mapper, query_entity, path, adapter, > > column_collection, with_polymorphic, only_load_props, > > polymorphic_discriminator, **kw) > > 257 column_collection=column_collection, > > 258 memoized_populators=quick_populators, > > --> 259 **kw > > 260 ) > > 261 > > > > .../lib/python2.7/site-packages/sqlalchemy/orm/interfaces.pyc in > setup(self, > > context, entity, path, adapter, **kwargs) > > 515 else: > > 516 strat = self.strategy > > --> 517 strat.setup_query(context, entity, path, loader, > adapter, > > **kwargs) > > 518 > > 519 def create_row_processor( > > > > .../lib/python2.7/site-packages/sqlalchemy/orm/strategies.pyc in > > setup_query(self, context, entity, path, loadopt, adapter, > > column_collection, parentmapper, **kwargs) > > 786 orig_query, leftmost_mapper, > > 787 leftmost_attr, leftmost_relationship, > > --> 788 entity.entity_zero > > 789 ) > > 790 > > > > .../lib/python2.7/site-packages/sqlalchemy/orm/strategies.pyc in > > _generate_from_original_query(self, orig_query, leftmost_mapper, > > leftmost_attr, leftmost_relationship, orig_entity) > > 859 ent['entity'] for ent in > > orig_query.column_descriptions > > 860 ])), > > --> 861 False > > 862 ) > > 863 > > > > .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in > > _set_select_from(self, obj, set_base_alias) > > 191 > > 192 for from_obj in obj: > > --> 193 info = inspect(from_obj) > > 194 if hasattr(info, 'mapper') and \ > > 195 (info.is_mapper or info.is_aliased_class): > > > > .../lib/python2.7/site-packages/sqlalchemy/inspection.pyc in > > inspect(subject, raiseerr) > > 73 "No inspection system is " > > 74 "available for object of type %s" % > > ---> 75 type_) > > 76 return ret > > 77 > > > > NoInspectionAvailable: No inspection system is available for object of > type > > <type 'NoneType'> > > > > > > In particular, a subqueried relationship (either through lazy='subquery' > or > > subqueryload(A.b)) causes .exists() to fail. We obviously don't call > > subqueryload(A.b) directly with exists queries, but we have models using > > lazy='subquery' that we use .exists() queries for. > > > > My current workaround is A.query.options(lazyload('*')).exists(). Does > this > > seem like a bug introduced in 1.1, or were there any intentional changes > > regarding use of .exists() queries? > > > > Thanks! > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > > > > http://www.sqlalchemy.org/ > > > > To post example code, please provide an MCVE: Minimal, Complete, and > > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > > description. > > --- > > 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:>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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.