How can I construct a count aggregation over a join with SqlAlchemy?

Question

I have a table of users, a table of groups that those users may belong to, and a join table between users and groups.

This is represented in SQLAlchemy as follows:

class User(Base):
    __tablename__ = 'user'
    user_id = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False)
    email = Column(String(250), nullable=False)
    groups = relationship('Group', secondary='user_group_pair')

class Group(Base):
    __tablename__ = 'group'
    group_id = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False)
    date_created = Column(String(250), nullable=False)
    members = relationship('User', secondary='user_group_pair')

class User_Group_Pair(Base):
    __tablename__ = 'user_group_pair'
    user_group_pair_id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.user_id'))
    group_id = Column(Integer, ForeignKey('group.group_id'))
    user = relationship(User, backref=backref("group_assoc"))
    group = relationship(Group, backref=backref("user_assoc"))

I'm trying to solve the following simple problem:

I want to write a query that will return a list of users along with the number of groups that each of them belongs to.

This requires data from both User and User_Group_Pair (thus why the title of my question refers to a join), and a count aggregation grouped by user_id.

I'm not sure why this won't work:

subq = session.query(User_Group_Pair.user_id.label('user_id'), func.count(User_Group_Pair.user_group_pair_id).label('count')).\
group_by(User_Group_Pair.user_id).order_by('count ASC').subquery()

result = session.query(User).join(subq, User.user_id == subq.user_id).all()

I get this error:

'Alias' object has no attribute 'user_id'

However, note that I have labelled User_Group_Pair.user_id with the label 'user_id'... Any thoughts?

Thank you

van · Accepted Answer · 2015-10-26 08:20:19Z

1

Just change subq.user_id to subq.c.user_id (c stands for columns) to make it work:

result = session.query(User).join(subq, User.user_id == subq.c.user_id).all()

But still you will get only those users which belong to at least one group, and the number of groups is not really returned in the result of the query. The query below is an approach to solve this issue:

q = (session.query(User, func.count(Group.group_id).label("num_groups"))
     .outerjoin(Group, User.groups)
     .group_by(User.user_id)
     )
for b, num_groups in q:
    print(b, num_groups)

answered Oct 26, 2015 at 8:20

van

77.6k13 gold badges174 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

r-m-n · Accepted Answer · 2015-10-26 08:18:41Z

1

http://docs.sqlalchemy.org/en/rel_1_0/orm/tutorial.html#using-subqueries

subquery() method on Query produces a SQL expression construct representing a SELECT statement embedded within an alias. The columns on the statement are accessible through an attribute called c.

You can use column names with .c.column_name in your query

result = session.query(User).join(subq, User.user_id == subq.c.user_id).all()

answered Oct 26, 2015 at 8:18

r-m-n

15.3k6 gold badges74 silver badges71 bronze badges

Collectives™ on Stack Overflow

How can I construct a count aggregation over a join with SqlAlchemy?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related