select distinct key and value counts in flask sqlalchemy ( flask restful API)

Question

i have trouble how to return key, and value counts from my DB table of workers, where each customer has given rating to workers. I want to return the rating of each worker. My DB models, end points are as below:

@classmethod
def distinct_rating_count(cls, _workerId):
    return {'rating_count': [c[0] for c in
                             db.session.query
                             (func.count(distinct(RatingModel.rating)))
                             .filter_by(workerId=_workerId)
                             .group_by(RatingModel.rating)]}

class RatingModel(db.Model):

__tablename__ = 'rating'
id = db.Column(db.Integer(), primary_key=True)
rating = db.Column(db.Integer(), nullable=False)
rateDate = db.Column(db.DateTime, default=datetime.datetime.utcnow)
comments = db.Column(db.String(512), nullable=True)

userId = db.Column(db.Integer(), db.ForeignKey('users.id'))
user = db.relationship('UserModel')
workerId = db.Column(db.Integer(), db.ForeignKey('workers.id'))
worker = db.relationship('WorkerModel')

def __init__(self, userId, workerId, rating, comments):

    self.userId = userId
    self.workerId = workerId
    self.rating = rating
    self.comments = comments

class DistinctRatingCount(Resource):

def get(self, workerId):
    ratingcount = RatingModel.distinct_rating_count(workerId)
    if ratingcount:
        return ratingcount
    else:
        return {'message': 'There is no rating yet'}

classmethod distinct_rating_count returns:

{ "rating_count": [ 1, 1, 1 ] }

However, I want some thing like dictionary, if there is no rating value for some workers, like in below example no one rated worker with rating of "1" and "4" then it should be labelled 0 times:

{"1":0,"2":3,"3":5,"4":0,"5":2}

I have tried a lot to search of stackoverflow and googled it , I could not get right clue. thanks in advance.

//////////////////////////////////////////////////////////////////////// finally according to @Shivendra Pratap Kushwaha technique with little modification, i sorted it out like below:

@classmethod def distinct_rating_count(cls, _workerId): rating = dict(cls.query.with_entities (RatingModel.rating, func.count( RatingModel.rating)) .group_by(RatingModel.rating) .filter_by(workerId=_workerId))

----------{ "1": 2, "4": 1, "5": 2 }

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

This might give you an example of how it can be done. In this case I am taking example of simple Student model:

Student Model :

1.)id

2.)subject

3.)school_id

Now I want to calculate student.id and corresponding count of subjects from all the rows having particular school_id.

I need some dictionay like CountStudDic = {"1":0,"2":3,"3":5,"4":0,"5":2} (As of yours)

So here is how to achieve it :

CountStudDic = dict(Student.query.with_entities(Student.id,func.count(Student.subject)).group_by(Student.subject).filter_by(school_id=str(current_user.id)).all())

The basic idea is to take both the entities and convert into dictionary.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Feb 24, 2020 at 6:24

Shivendra Pratap Kushwaha

2,0211 gold badge11 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shaukat hussain Over a year ago

thank you so much, your technique worked for me with little modification. you are super!

Collectives™ on Stack Overflow

select distinct key and value counts in flask sqlalchemy ( flask restful API)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related