SQLAlchemy group_by() summation help needed for simple linked tables

Question

I've looked around for help on similar topics but could not quite find the right solution to my problem which deals with SQLAlchemy/Flask-SQLAlchemy.

Here is the situation: I have 3 tables that deal with raffle ticket data. The tables are the following: A raffles table:

class RaffleModel(db.Model):
    __tablename__ = 'raffles'

    id = db.Column(db.Integer, primary_key=True)

    # relationship to users
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    user = db.relationship("UserModel", backref="raffles")

    # relationship to ticket_md
    ticket_md_id = db.Column(db.Integer, db.ForeignKey('ticket_mds.id'))
    ticket_md = db.relationship("TicketMetaDataModel", backref="raffles")

    def __init__(self, user, ticket_md):
        self.user = user
        self.ticket_md = ticket_md

With the following sample data:

id    user_id    ticket_md_id
1     1          1
2     2          1
3     3          1
4     1          2
5     2          2

A users table:

class UserModel(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.String(25))
    last_name = db.Column(db.String(25))
    won = db.Column(db.Boolean, default=False, nullable=False)

    # has backref via user.raffles

    def __init__(self, first_name, last_name, won):
        self.first_name = first_name
        self.last_name = last_name
        self.won = won

With the following sample data:

id    first_name    last_name    won
1     Rob           Stark        False
2     Stan          Clark        False
3     Tom           Dark         False

And lastly a ticket_mds table:

class TicketMetaDataModel(db.Model):
    __tablename__ = 'ticket_mds'

    id = db.Column(db.Integer, primary_key=True)
    raffle_type = db.Column(db.String(20))
    description = db.Column(db.String(200))
    modifier = db.Column(db.Integer)

    # has backref via ticket_md.raffles

    def __init__(self, raffle_type, description, modifier):
        self.raffle_type = raffle_type
        self.description = description
        self.modifier = modifier

With the following sample data:

id    raffle_type    description    modifier
1     type_1         blah blah      21
2     type_2         blah blah      5

My issue here lies in the fact that I can't seem to quite figure out a query to get the sum of ticket points (based on the TicketMetaDataModel.modifier attribute) grouped by the user from the raffles table to return results in the following format:

first_name, last_name, total_points,
e.g.:

Rob    Stark    26
Stan   Clark    26
Tom    Dark     21

I have tried the following (but have not been able to get further from there):

query = db.session.query(RaffleModel.id, UserModel.first_name, UserModel.last_name, TicketMetaDataModel.modifier)
query = query.join(UserModel).join(TicketMetaDataModel)
results = query.all()
results equals:

[(1, 'Rob', 'Stark', 21), (2, 'Stan', 'Clark', 21), (3, 'Tom', 'Dark', 21),(4, 'Rob', 'Stark', 5), (5, 'Stan', 'Clark', 5)]

bluesmonk · Accepted Answer

It got me intrigued and I did a self contained example

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, ForeignKey, String, Boolean
from sqlalchemy.orm import relationship, sessionmaker

from pprint import pformat as pf

Base = declarative_base()

class ReprMixin(object):

    """A mixin to implement a generic __repr__ method"""

    def as_dict(self):
        """return instance as a dictionary"""
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}

    def __repr__(self):
        return '<%s(%s)>' % (self.__class__.__name__, ', '.join([f'{c.name} = {getattr(self, c.name)}' for c in self.__table__.columns]))

class RaffleModel(ReprMixin, Base):
    __tablename__ = 'raffles'

    id = Column(Integer, primary_key=True)

    # relationship to users
    user_id = Column(Integer, ForeignKey('users.id'))
    user = relationship("UserModel", backref="raffles")

    # relationship to ticket_md
    ticket_md_id = Column(Integer, ForeignKey('ticket_mds.id'))
    ticket_md = relationship("TicketMetaDataModel", backref="raffles")

    def __init__(self, user, ticket_md):
        self.user = user    
        self.ticket_md = ticket_md

    @classmethod
    def insert_defaults(cls, session):
        for user_id, ticket_id in zip([1,2,3,1,2],[1,1,1,2,2]):
            user = session.query(UserModel).filter_by(id=user_id).scalar()
            ticket = session.query(TicketMetaDataModel).filter_by(id=ticket_id).scalar()
            session.add(cls(user, ticket))
        session.commit()


class UserModel(ReprMixin, Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    first_name = Column(String(25))
    last_name = Column(String(25))
    won = Column(Boolean, default=False, nullable=False)

    # has backref via user.raffles

    def __init__(self, first_name, last_name, won):
        self.first_name = first_name
        self.last_name = last_name
        self.won = won

    @classmethod
    def insert_defaults(cls, session):
        for first_name, last_name, won in zip("Rob Stan Tom".split(),"Stark Clark Dark".split(), [False]*3):
            session.add(cls(first_name, last_name, won))
        session.commit()


class TicketMetaDataModel(ReprMixin, Base):
    __tablename__ = 'ticket_mds'

    id = Column(Integer, primary_key=True)
    raffle_type = Column(String(20))
    description = Column(String(200))
    modifier = Column(Integer)

    # has backref via ticket_md.raffles

    def __init__(self, raffle_type, description, modifier):
        self.raffle_type = raffle_type
        self.description = description
        self.modifier = modifier

    @classmethod
    def insert_defaults(cls, session):
        for raffle_type, description, modifier in zip("type1 type2".split(), "desc1 desc2".split(), [21,5]):
            session.add(cls(raffle_type, description, modifier))
        session.commit()

if __name__ == '__main__':


    from pprint import pprint as pp
    engine = create_engine('sqlite://')
    Session = sessionmaker()

    sess = Session(bind=engine)

    Base.metadata.create_all(bind=engine)

    TicketMetaDataModel.insert_defaults(sess)
    UserModel.insert_defaults(sess)
    RaffleModel.insert_defaults(sess)


    print('************************')
    pp(sess.query(UserModel).all())
    print('************************')
    pp(sess.query(RaffleModel).all())
    print('************************')
    pp(sess.query(TicketMetaDataModel).all())


    from sqlalchemy import func
    query = sess.query(UserModel.first_name, UserModel.last_name, func.sum(TicketMetaDataModel.modifier))

    query2 = query.join(RaffleModel.user)\
                .join(RaffleModel.ticket_md)\
                .group_by(UserModel.first_name, UserModel.last_name)

    print('************************')
    print(query)
    print(query.all())
    print('************************')
    print(query2.all())
    print(query2)

    Base.metadata.drop_all(engine)

running it it returns

************************
[,
 ,
 ]
************************
[,
 ,
 ,
 ,
 ]
************************
[,
 ]
************************
SELECT users.first_name AS users_first_name, users.last_name AS users_last_name, sum(ticket_mds.modifier) AS sum_1 
FROM users, ticket_mds
[('Tom', 'Dark', 78)]
************************
[('Rob', 'Stark', 26), ('Stan', 'Clark', 26), ('Tom', 'Dark', 21)]
SELECT users.first_name AS users_first_name, users.last_name AS users_last_name, sum(ticket_mds.modifier) AS sum_1 
FROM raffles JOIN users ON users.id = raffles.user_id JOIN ticket_mds ON ticket_mds.id = raffles.ticket_md_id GROUP BY users.first_name, users.last_name

It seems that your model would benefit greatly if RaffleModel were an association table for a many to many relationship between UserModel and TicketMetadataModel

SQLAlchemy group_by() summation help needed for simple linked tables

Answers (2)

Related Questions