CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
shurup
shurup

Reputation: 871

How to get number of distinct column entries by user in Flask-SQLAlchemy?

I have a database of user activities. For a specific user, I want to return the number of distinct types of activities they did. For example:

Bob:
Activity 1: type A
Activity 2: Type B
Activity 3: Type A

The result should be 2 because of two distinct activities (A and B).

I tried the following command:

Activity.activity_type.query.filter(Activity.user_id==1).distinct(Activity.activity_type)

The Activity.activity_type part is giving an error. I look on StackOverflow and documentation, but could not find how to properly use distinct in this situation. I would appreciate help on how to get this query to work.

Upvotes: 0

Views: 144

Answers (1)

SuperShoot
SuperShoot

Reputation: 10871

Please excuse my original interpretation of your objective, it was not correct as returned a count of each activity attempted, not a count of distinct activities attempted.

See below for a solution to the problem which was supplied by Ilja Everilä in the comments.

However, the exception that you are getting occurs before your query is executed (I think, as I've reverse engineered your models from the query that you've provided) so the bulk of this answer is focused on that:

AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Activity.activity_type has an attribute 'query'

I created these models in order to execute your query:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(16))

class Activity(db.Model):
    activity_id = db.Column(db.Integer, primary_key=True)
    activity_type = db.Column(db.String(32))
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    user = db.relationship('User')

Then wrote a little script to add some data:

if __name__ == '__main__':
    with app.app_context():
        db.drop_all()
        db.create_all()
        names = ['batman', 'a', 'list', 'of', 'names']
        users = [User(name=name) for name in names]
        db.session.add_all(users)
        activities = [
            'Self Doubt',
            'Dark Introspection',
            'Gotham Needs Me',
            'Training Montage',
            'Fight Crime',
            'Get the girl'
        ]
        db.session.add_all(
            [Activity(
                activity_type=random.choice(activities),
                user=random.choice(users)
            ) for _ in range(20)]
        )

        db.session.commit()

Executing Activity.activity_type.query.filter(Activity.user_id==1).distinct(Activity.activity_type) raises the aforementioned error.

The problem comes from this part of your query Activity.activity_type.query. It's saying that it's looked for an attribute called 'query' on Activity.activity_type but couldn't find it. Breaking the error message down a bit:

We know that Activity.activity_type is an instance of Column when we define it on the model, but print(Activity.activity_type) shows <class 'sqlalchemy.orm.attributes.InstrumentedAttribute'>, so that's the first object referenced in the error. The Comparitor is an internal sqlalchemy construct that is also checked (you can find it with Activity.activity_type.comparator if you're interested). So sqlalchemy looked for, and couldn't find, an attribute with the name 'query' on the Activity.activity_type column.

The .query property comes from the db.Model base that your models inherit from and will only be available to objects in the same MRO as db.Model. Because the column objects don't inherit from db.Model, you cannot access the query property through them. Activity.query is legal, Activity.activity_type.query is not.

This is the query that Ilja provided in comment to my original answer which is the correct solution:

        batman_activity_count = db.session.query(
            db.func.count(
                Activity.activity_type.distinct()
            )
        ).filter(Activity.user_id==1).scalar()

        print(batman_activity_count)  # random integer b/w 0 and 6.

Upvotes: 1

Related Questions