Flask-SQLAlchemy - Grouping "one to many" Relationship results

Question

Well i'm having a hard time figuring out how i'm going to group the relationship rows with a sum to print out as "total" on my template, perhaps i'm searching with the wrong terms. here is my models

class Venda(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    firma = db.Column(db.Integer, db.ForeignKey('firma.id'), nullable=False)
    caixa_id = db.Column(db.Integer, db.ForeignKey('caixa.id'), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('usuario.id'), nullable=False)
    cliente_id = db.Column(db.Integer, db.ForeignKey('cliente.id'), nullable=False)
    data_venda = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    vendedor = db.Column(db.Integer, db.ForeignKey('usuario.id'), nullable=False)
    desconto = db.Column(db.Numeric(11,3), nullable=False, default=0.0)
    acrescimo = db.Column(db.Numeric(11,3), nullable=False, default=0.0)
    qtd_itens = db.Column(db.Integer, nullable=False, default=0)
    cupomdesc = db.Column(db.String(13))
    total = db.Column(db.Numeric(11,3),nullable=False,default=0.0)
    hash = db.Column(db.String(32),unique=True,nullable=False)
    deletado = db.Column(db.Boolean,nullable=False, default= 0)
    produtos = db.relationship('VendaProduto', backref='prods_id', lazy=True)
    pagamentos = db.relationship('VendaPagamento', backref='pgtos_id', lazy=True)
    DFe =  db.relationship('VendaDfe', backref='dfe_id', lazy=True)

class VendaPagamento(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    firma = db.Column(db.Integer, db.ForeignKey('firma.id'), nullable=False)
    venda_id = db.Column(db.Integer, db.ForeignKey('venda.id'), nullable=False)
    tipo = db.Column(db.Integer, nullable=False, default = 1)
    bandeira = db.Column(db.String(32), nullable=False)
    valor = db.Column(db.Numeric(11,3),nullable=False,default=0.0)
    data_vencimento = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)

So i have to group Venda.pagamentos by VendaPagamento.tipo and sum VendaPagamento.valor.

Writing pure sql i would join it and group by the joined field, and have the other joined field inside a sum function, but i searched the sql alchemy documentation and i can't seem to find how to do it.

Top-level documentation chapter: https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html — Florin C., Oct 24 '22 at 13:28

score 1 · Accepted Answer · answered Oct 24 '22 at 13:32

1

The SQLAlchemy ORM interface has a group_by function which should cover what you need. I think the term you need to search for is ORM or object relational mapping to find these kind of solutions.

Here's a similar answer that covers using group by.

answered Oct 24 '22 at 13:32

barryodev

509
3
11

Thanks, later i will try it on, but from what i understood it will be something like `vendas = Venda.query.with_entities(func.sum(Venda.pagamentos.valor).group_by(VendaPagamento.tipo)).filter(Venda.data_venda.between(dataini,datafim), Venda.firma == firma_atual).paginate(page=page, per_page=50)` – Rodrigo P. Oct 24 '22 at 13:41

Flask-SQLAlchemy - Grouping "one to many" Relationship results

1 Answers1