Count related items in a sqlalchemy model using ChoiceType

Question

This is a follow up to a previous question here. I'd like to count the number of offers, in each category, and output them in a format, which I can iterate in Jinja.

new, 3
used, 7
broken, 5

Here's what I've got right now:

class Offer(Base):
    CATEGORIES = [
        (u'new', u'New'),
        (u'used', u'Used'),
        (u'broken', u'Broken')
    ]

    __tablename__ = 'offers'
    id = sa.Column(sa.Integer, primary_key=True)
    summary = sa.Column(sa.Unicode(255))
    category = sa.Column(ChoiceType(CATEGORIES))

Following the previous answer, I tried something like this:

count_categories = db.session.query(
        CATEGORIES.value, func.count(Offer.id)).outerjoin(
        Offer).group_by(CATEGORIES.key).all()

This obviously doesn't work because CATEGORIES.value is not defined; How can I pass CATEGORIES to this query, to yield the desired result? The "setup" seems fairly common, and is taken straight from the SQLAlchemy-Utils Data types page

Your help is much appreciated (growing white hair already)!

A horrible but working, temporary work-around:

result = []
for category in Offer.CATEGORIES:
    count = db.session.query(func.count(Offer.id)).filter_by(category=category[0]).all()
    result.append((category[0], category[1], count[0][0]))

You are very close to having solved this yourself. You know you need to do an outer join between categories and offers and that the outer join you are effectively performing in application code is ugly. Allow the database to do the hard work for you by moving categories into its own table joined by a foreign key to the offers table. You can now perform the outer join you want and also easily add new categories in the future if needed. — EAW, Apr 19 '19 at 22:23
@EAW Thanks for your feedback! You're right; that's probably the only way, to offload this to the database. I was really hoping, that I could somehow merge this, with the `db.session.query`; potentially as a _virtual table_. If I find a solution, I'll post back here. — Franz, Apr 21 '19 at 16:46

score 0 · Answer 1 · answered Apr 23 '19 at 11:06

To count the number of offers for each category you need to do an outer join between categories and offers. Given that you are not storing categories as a database table you are having to do this in application code which is not ideal. You just need to create a new categories table and replace the category field in the user table with a foreign key joining to the new categories table. The following code demonstrates this.

class Offer(Base):
    __tablename__ = 'offers'
    id = sa.Column(sa.Integer, primary_key=True)
    summary = sa.Column(sa.Unicode(255))
    category_id = sa.Column(sa.Integer, sa.ForeignKey("categories.id"))
    category = sa.orm.relationship("Category", back_populates="offers")

class Category(Base):    
    __tablename__ = 'categories'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.Unicode(6), unique=True)
    offers = sa.orm.relationship("Offer", back_populates="category")

# populate categories with the same values as your original enumeration
session.add(Category(name="New"))
session.add(Category(name="Used"))
session.add(Category(name="Broken"))

count_categories = session.query(Category.name, func.count(Offer.id)). \
    select_from(Category).outerjoin(Offer).group_by(Category.name).all()

I appreciate your feedback, but this doesn't answer my question. I've specifically stated, that this is a follow-up to [another question](https://stackoverflow.com/questions/39040019/count-related-items-in-a-sqlalchemy-model). I might agree, that it is not ideal, however, I've found this in the official docs and since this setup is easier to deal with _for now_ and supports _easy gettext_ style translations, I'd like to see whether it's possible - at least for development - **if it's possible**. — Franz, Apr 24 '19 at 17:22
In answer to your question "How can I pass CATEGORIES to this query, to yield the desired result?", the only way is to put it in a database table. If you insist on keeping it in a python variable then the database has no way of knowing what the values are and cannot therefore use it in a query. See this question for more information on using enumerations vs tables https://stackoverflow.com/q/433490/10548137 — EAW, Apr 25 '19 at 19:00

Count related items in a sqlalchemy model using ChoiceType

1 Answers1