I get the wrong record set by adding an aggregate function like func.sum to a dynamic relationship. I have demonstrated the sample code below to demonstrate this.
from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import ( relationship, scoped_session, sessionmaker, backref ) from sqlalchemy import ( create_engine, Table, Column, Integer, String, ForeignKey, func ) from zope.sqlalchemy import ZopeTransactionExtension import transaction Base = declarative_base() DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) userid = Column(String(15), unique=True, nullable=False) article_list = relationship("Article", backref="user", lazy="dynamic") class Tag(Base): __tablename__ = 'tags' id = Column(Integer, primary_key=True) name = Column(String(25), nullable=False, unique=True) class Article(Base): __tablename__ = 'articles' id = Column(Integer, primary_key=True) title = Column(String(25), nullable=False) duration = Column(Integer) user_id = Column(Integer, ForeignKey('users.id'), nullable=False) tags = relationship('Tag', secondary="tag_map", backref=backref("article_list", lazy="dynamic")) tag_map_table = Table( 'tag_map', Base.metadata, Column('tag_id', Integer, ForeignKey('tags.id'), nullable=False), Column('article_id', Integer, ForeignKey('articles.id'), nullable=False)) engine = create_engine('sqlite:///tag_test.sqlite') DBSession.configure(bind=engine) Base.metadata.create_all(engine) with transaction.manager: t1 = Tag(name='software') t2 = Tag(name='hardware') john = User(userid='john') a1 = Article(title='First article', duration=300) a1.user = john a1.tags.append(t1) a1.tags.append(t2) DBSession.add(a1) a2 = Article(title='Second article', duration=50) a2.user = john a2.tags.append(t1) a2.tags.append(t2) DBSession.add(a1)
As we see above in the code, I added two tags for both articles. Now I want to request articles written by the user "John", grouped by tags with him. I want to find the sum of each tag duration.
john = DBSession.query(User).filter(User.userid=='john').first() res = john.article_list.join(Article.tags).add_column( func.sum(Article.duration)).group_by(Tag.id) for article, tsum in res: print ("Article : %s, Sum duration : %d" % (article.title, tsum))
The request created for res ,
SELECT articles.id AS articles_id, articles.title AS articles_title, articles.duration AS articles_duration, articles.user_id AS articles_user_id, sum(articles.duration) AS sum_1 FROM articles JOIN tag_map AS tag_map_1 ON articles.id = tag_map_1.article_id JOIN tags ON tags.id = tag_map_1.tag_id WHERE :param_1 = articles.user_id GROUP BY tags.id
which when executed directly in the sqlite database gives two rows corresponding to two tags
2|Second article|50|1|350 2|Second article|50|1|350
While the results returned by SQLAlchemy reflect only one row
Article: Second Article, Amount Amount: 350
But if I add an extra column to contain the tag name in the AppenderQuery object
res = john.article_list.join(Article.tags).add_column(Tag.name).add_column( func.sum(Article.duration)).group_by(Tag.id) for article, tag_name, tsum in res: print ("Article : %s, Tag : %s, Sum duration : %d" % ( article.title, tag_name, tsum))
I get the right results
Article : Second article, Tag : software, Sum duration : 350 Article : Second article, Tag : hardware, Sum duration : 350
So, what is the proper way to use aggregated functions in an AppenderQuery object to get categorized results?