On Wed, Jul 11, 2018 at 8:46 AM, Manoj Mokashi <manojmoka...@gmail.com> wrote: > Firstly, thanks for making this software available. > > My question is related to this issue, probably a common one for newbies : > > https://stackoverflow.com/questions/16589208/attributeerror-while-querying-neither-instrumentedattribute-object-nor-compa > > Consider the following entities : > > > class Foo(Base): > __tablename__ = "foo" > id = Column(Integer, primary_key=True) > name = Column(String, unique = True) > > class Bar(Base): > __tablename__ = "bar" > id = Column(Integer, primary_key = True) > foo_id = Column(Integer, ForeignKey("foo.id")) > > foo = relationship("Foo", lazy="joined") > > ses.query(Bar).filter(Bar.foo.name == "blah") throws > > AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' > object associated with Bar.foo > > has an attribute 'name' > > ( Note : i have edited the example somewhat ) > > > From the answers, it seems that i cannot use Bar.foo.name, since foo is a > relationship. > > Also, i have to explicitly join Foo to the query in order to use its > attributes in filter or order-by. > > However, when i do this, and i have lazy="joined" in the relationship, > > the relation too creates a join (which cannot be used in the filter, > orderby), > > plus the join i have already added in the query. > > My questions are related to this : > > > 1. Why don't we allow the declared relations to be available in the query > for filter/order-by etc ?
they are, you just need to name the entities explicitly in most cases: query(Bar).join(Bar.foo).filter(Foo.name == 'blah').order_by(Foo.name) you can use filter_by() which will resolve to the most recent join: query(Bar).join(Bar.foo).filter_by(name == "blah") > > Does this not undermine the power of declarative relations ? no? the relationship is working out a whole JOIN for you, even if you are using aliases, mappings to SELECT statements, all kinds of things. the complexity of relationship() is enormous. > > Can we declare a list of columns to be fetched for a relation ? sure! query(Bar, Foo.one, Foo.two).join(Bar.foo) > > 2. Given that we might have to add filter conditions to queries over time, > what is the best-practice for loading > > declared relations ? Should they have lazy="noload" or default(lazy-loading) never use "noload". Leave it at the default, and if you really don't want those lazy loads to emit later, use lazy="raise", to catch code that might be accessing it after the fact. > > and we use explicit joins in queries to avoid the duplicate joins ? the duplicate joins are there for a reason - you might want to join in one way for filtering the results, yet you still want to load collections fully. I think the main two sections that should help understand better are: http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html?highlight=raiseload#the-zen-of-joined-eager-loading http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html?highlight=raiseload#routing-explicit-joins-statements-into-eagerly-loaded-collections > > 3. If i add a filter in the query for Foo without creating a join, it adds > Foo in the FROM clause, > > but without any join condition. > > Is this a bug ? not at all, you've added two FROM elements to the statement, and not told it how/if to join them, it does what you told it. There could be ten different FROM clauses, all joinable in any number of combinations. Should the Query guess how you want to join these? > > > regards, > > manoj > > -- > 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. -- 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.
