CODEWITHSUNDEEP
pythonpostgresqlflasksqlalchemy
Stephen
Stephen

Reputation: 60

How to replace group_by in sqlalchemy query so that postgresql database does not throw an error

I had a working query in my flask file until I tried to deploy my app to heroku. The reason it was working is that I was using sqlite3 database locally but have to use postgresql database in production mode.

The issue I am having and can't resolve is the following sqlalchemy query: 

companies = StaffMember.query.filter_by(user_id=current_user.id).group_by(StaffMember.company).all()

When I run this in postgresql, I get the following error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.GroupingError) column "staffMembers.id" must appear in the GROUP BY clause or be used in an aggregate function

From what I have found out, the issue is that group_by does not work the same in postgresql and that I should try and use the keyword 'distinct' instead. But I don't really know a way to use distinct correctly to produce a list of companies that are unique in sqlalchemy.

I have tried things like: 

companies = StaffMember.query.filter_by(user_id=current_user.id).distinct(StaffMember.company).all()

or:

companies = StaffMember.query.filter_by(user_id=current_user.id).filter_by(StaffMember.company).distinct()

I don't think I am creating the query correctly.

I have tried to read the docs, but I cannot really understand them at my current coding level.

Upvotes: 2

Views: 692

Answers (1)

mathiascg
mathiascg

Reputation: 570

If you are trying to get a list of all distinct companies in a Table, you can do:

companies = StaffMember.query.with_entities(
     StaffMember.company
).filter_by(
    user_id=current_user.id
).distinct()

Or also:

companies = session.query(
     StaffMember.company
).filter_by(
    user_id=current_user.id
).distinct()

In this case, using group by or distinct will produce the same result. If you want to use group by, all selected columns that are not aggregated must be inside .group_by()

Upvotes: 4

Related Questions