CODEWITHSUNDEEP
pythonsqlalchemy
Luiz Domingues
Luiz Domingues

Reputation: 369

Sorting a Flask-SQLAlchemy model query by the count of child rows in one-to-many?

I have a Product Model:

class Product(db.Model):
    __tablename__ = 'product'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    offers = db.relationship('Offer', back_populates='product', lazy=True)

and a Offer Model:

class Offer(db.Model):
    __tablename__ = 'offer'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'), nullable=False)
    product = db.relationship('Product', back_populates='offers')

I would like to know how can I order a list of Products by the amount of offers each product has. I would like to be able to do something like:

sort = request.args.get('sort') # asc / desc
products = Product.query.order_by(len(Product.offers).sort).pagination([...])

I tried doing this but it only returns one Product:

query = db.session.query(Product).join(Product.offers)
if order == 'desc':
    query = query.order_by(desc(func.count(Product.offers)))
else:
    query = query.order_by(func.count(Product.offers))
query = query.filter(Product.has_offers == True)
products = query.paginate(page=page, per_page=50)



I will use this to be able to sort a HTML table by clicking on the table header. I am also sorting through other informations:

sort_by = dict(id=Product.id, mp_id=Product.marketplace_id,
                   updated=Product.last_seen, name=Product.name)
if sort_id in sort_by.keys():
    sort = sort_by[sort_id]
    sort = sort.desc() if order == 'desc' else sort.asc()
    query = query.order_by(sort)
query = query.filter(Product.has_offers == True)
products = query.paginate(page=page, per_page=50)

Upvotes: 2

Views: 1456

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52929

I tried doing this but it only gives me one Product:

You must be using MySQL 5.6 or older, since newer versions will not accept that query and raise an error:

ERROR 3029 (HY000): Expression #1 of ORDER BY contains aggregate function and applies to the result of a non-aggregated query

See the answers in "What can an aggregate function do in the ORDER BY clause?" for an explanation as to why you are getting one Product as a result.

Your original approach is not that far from a working solution in the end. All you need to do is to add a suitable GROUP BY clause that produces deterministic results in your query for it to work correctly:

order_op = desc if order == 'desc' else asc

query = db.session.query(Product).\
    join(Product.offers).\
    filter(Product.has_offers).\
    group_by(Product.id).\
    order_by(order_op(func.count()))

products = query.paginate(page=page, per_page=50)

The selected columns are determined by Product.id, so you don't get indeterminate results.

Upvotes: 2

Related Questions