query.options(joinedload()) is not used for creating joins, you need you use query.join().
See http://docs.sqlalchemy.org/en/rel_0_9/faq.html#i-m-using-joinedload-or-lazy-false-to-create-a-join-outer-join-and-sqlalchemy-is-not-constructing-the-correct-query-when-i-try-to-add-a-where-order-by-limit-etc-which-relies-upon-the-outer-join <http://docs.sqlalchemy.org/en/rel_0_9/faq.html#i-m-using-joinedload-or-lazy-false-to-create-a-join-outer-join-and-sqlalchemy-is-not-constructing-the-correct-query-when-i-try-to-add-a-where-order-by-limit-etc-which-relies-upon-the-outer-join> to start and then http://docs.sqlalchemy.org/en/rel_0_9/orm/loading.html#the-zen-of-eager-loading <http://docs.sqlalchemy.org/en/rel_0_9/orm/loading.html#the-zen-of-eager-loading> which attempts to explain the difference. > On Nov 2, 2014, at 3:28 PM, nathan <nathanma...@gmail.com> wrote: > > I’m trying to get a simple join working to a parent table with group_by. I’ve > tried some variations working from the ORM tutorial but keep running into > exceptions or I get multiple queries. > > I’m trying to get a single query that results in a collection of Client > instances that’s joined to and grouped by the parent Affiliate. So an > Affiliate has one or more Clients. And I’d like to have a report like: > > Affiliate 1: > Client 1 > Client 2 > Affiliate 2: > Client 3 > Client 4 > > Here’s the model: > > class Affiliate(Base): > __tablename__ = 'affiliate' > id = Column(Integer, primary_key=True) > name = Column(Unicode(50), unique=True, nullable=False, index=True) > > class Client(Base): > __tablename__ = 'client' > id = Column(Integer, primary_key=True) > affiliateid = Column(Integer, ForeignKey('affiliate.id'), nullable=False, > index=True) > name = Column(Unicode(50), unique=False, nullable=False, > index=True) > affiliate = relationship('Affiliate', lazy='select’) > > I’ve tried variations on the code below, but here’s what I have now: > > DBSession.\ > query(Client).\ > options(joinedload('affiliate')).\ > group_by(Affiliate).\ > order_by(Client.name).\ > all() > > and the exception: > > ProgrammingError: (ProgrammingError) invalid reference to FROM-clause entry > for table "affiliate" > LINE 2: ...1 ON affiliate_1.id = client.affiliateid GROUP BY affiliate.... > ^ > HINT: Perhaps you meant to reference the table alias "affiliate_1". > 'SELECT client.id AS client_id, client.affiliateid AS client_affiliateid, > client.name AS client_name, affiliate_1.id AS affiliate_1_id, > affiliate_1.name AS affiliate_1_name, \nFROM client LEFT OUTER JOIN affiliate > AS affiliate_1 ON affiliate_1.id = client.affiliateid GROUP BY affiliate.id, > affiliate.name, ORDER BY client.name' {} > > The “HINT” is awesome, but unfortunately I’m not clueful enough to use it. :( > > Thanks > > > -- > 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 http://groups.google.com/group/sqlalchemy > <http://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. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
