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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
