Flask-sqlalchemy - count different group categories in a single query

Question

I am trying to get counts of different object categories in a SQLAlchemy query. My data is organized into 3 tables:

class Meeting(db.Model):
    __tablename__ = 'meetings'
    id = db.Column(db.Integer,
                   primary_key=True)
    submissions = db.relationship('Submission',
                        backref='timeslot',
                        lazy=True)

class Submission(db.Model):
    __tablename__ = 'submissions'
    id = db.Column(db.Integer,
           primary_key=True,
           nullable=False)
    meeting_id =  db.Column(db.Integer,
            db.ForeignKey('meetings.id'),
                        nullable=False)
    _type = db.relationship('Act_types', lazy=True)
    _type_id = db.Column(db.Integer, db.ForeignKey('acttypes.id'), Nullable=False)


class Act_types(db.Model):
    __tablename__ = 'acttypes'
    id = db.Column(db.Integer, primary_key=True)
    action_type = db.Column(db.String(60))

The logic is the following: Users to make Submissions for a particular Meeting. Every Submission belongs to a particular Act_type:

Meeting->Submission->Act_Type

For every Meeting, I want to get information on how many submissions of a particular type are present.

Meeting -> Act_Type1-> count
Meeting -> Act_Type2-> count

etc

I want to get information with a SQLAlchemy query and not use loops.

Currently, in the database, I have a single meeting object, 6 submissions belonging to 2 types:

<Meeting 1>
[<Submission 1>, <Submission 2>, <Submission 3>, <Submission 4>, <Submission 5>, <Submission 6>]
[<Act_types 1>, <Act_types 2>]

if I do a plain select query I end up with the following:

subs = db.session.query(Meeting, Submission, Act_types).\
    filter(Meeting.id == self.id).\
    order_by(Act_types.action_type).\
    group_by(Act_types.action_type).\
    all()

> [(<Meeting 1>, <Submission 6>, <Act_types 2>), (<Meeting 1>, <Submission 6>, <Act_types 1>)]

I tried Group by & count function in sqlalchemy but it didn't give me what I wanted and just calculated the numbers of Act_Types entries.

<Meeting 1>
[(<Meeting 1>, <Submission 6>, <Act_types 2>, 6), (<Meeting 1>, <Submission 6>, <Act_types 1>, 6)]

The desired output is something like:

[(<Meeting 1>, <Act_types 2>, 4), (<Meeting 1>, <Act_types 1>, 2)]

How do I achieve that?

Ilja Everilä · Answer 1 · 2019-09-03T07:18:02.610

Since Act_types.action_type is not a key (primary key or unique), you cannot group by it and select all action type columns, since they are not determined by Act_type.action_type – the results would be indeterminate. Instead you can limit the the select to the action type alone. In addition you need joins in order to relate the meetings with submissions and action types:

subs = db.session.query(Meeting, Act_types.action_type, func.count()).\
    join(Submission, Meeting.submissions).\
    join(Act_types).\
    filter(Meeting.id == self.id).\
    order_by(Meeting.id, Act_types.action_type).\
    group_by(Meeting.id, Act_types.action_type).\
    all()

The Meeting.id in the GROUP BY clause might seem superfluous since in the end you're selecting only a single meeting, but to the DB there's no difference in selecting 1 or all and so you must include it. On the other hand since you already have the meeting object (you're using its id attribute), you could just omit it from the select list:

subs = db.session.query(Act_types.action_type, func.count()).\
    select_from(Submission).\
    join(Act_types).\
    filter(Submission.meeting_id == self.id).\
    order_by(Act_types.action_type).\
    group_by(Act_types.action_type).\
    all()

Flask-sqlalchemy - count different group categories in a single query

1 Answers1