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. 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.