Query sqlalchemy and count distinct results
I want to write a query to count how many items a User has organized by Product.title.
This function gives exactly what I want. I feel like there is a way I can use func.count to make this into 1 line.
def cart(self):
items = {}
for product in Product.query.join(Item).filter(Item.user==self):
item=Item.query.filter(Item.user==self, Item.product==product)
items[product.title] = {'id': product.id,'count': item.count(), 'cost': product.cost}
return items
This is my desired return. I've tried joining Product and Item, but I just get the distinct Product returns with no ability to count. Any suggestions?
Product.title: count
Apples: 10
Bananas: 5
Hotdogs: 1
Tables:
class Item(db.Model):
__tablename__ = 'item'
id = db.Column(db.Integer, primary_key=True)
product_id = db.Column(db.Integer, db.ForeignKey('product.id'), nullable=False)
product = db.relationship("Product")
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
user = db.relationship("User")
class Product(db.Model):
__tablename__ = 'product'
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String, nullable=False)
items = db.relationship('Item', back_populates='product', lazy='dynamic')
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
items = db.relationship('Item', back_populates='user', lazy='dynamic')
Comments
Post a Comment