flask-restless
flask-restless copied to clipboard
group_by with subset of columns doesn't work with postgres backend
Postgres requires that group_by columns are the only columns included in the SELECT query. Other backends like MySQL and SQLite let this slide picking a random row for the columns not in the GROUP BY.
Ex: http://stackoverflow.com/a/18039886/709040
I think that the sqlalchemy query should be limited by include_columns or excluded_columns.
Thank you so much for this wonderful package! The search api is incredibly flexible and I would love the group_by feature to work for me.
Found a work around or maybe this is intentional? If I specify the columns I want in a custom query and pass in a new order_by so it's not by the default (id) then it works!
I realize this is old, but in order to fix the bug, I need a brief minimal working example that demonstrates the issue, what the input is, what the output is, and what you expected the output to be. (Anyone who has this problem can jump in here to help out!)
I'm facing the same issue. Following minimal example to reproduce it with Postgres:
import flask
import flask_sqlalchemy
import flask_restless
app = flask.Flask(__name__)
app.config['DEBUG'] = True
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgres://postgres:[email protected]:1234/postgres'
db = flask_sqlalchemy.SQLAlchemy(app)
class Person(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String)
surname = db.Column(db.String)
db.create_all()
manager = flask_restless.APIManager(app, flask_sqlalchemy_db=db)
manager.create_api(Person, methods=['GET', 'POST', 'DELETE'])
app.run()
Calling /api/person?q={"group_by":[ {"field":"surname"}]}
after adding some data results in following exception:
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) column "person.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT person.id AS person_id, person.name AS person_name, p...
^
[SQL: 'SELECT person.id AS person_id, person.name AS person_name, person.surname AS person_surname \nFROM person GROUP BY person.surname ORDER BY person.id ASC \n LIMIT %(param_1)s'] [parameters: {'param_1': 2}]
127.0.0.1 - - [13/Dec/2017 09:12:18] "GET /api/person?q={%22group_by%22:[%20{%22field%22:%22surname%22}]} HTTP/1.1" 400 -
Not sure if this helps but here's the full stacktrace anyway:
Traceback (most recent call last):
File "C:\Users\user\PycharmProjects\repro\venv\lib\site-packages\flask_restless\views.py", line 189, in wrapped
return func(*args, **kw)
File "C:\Users\user\PycharmProjects\repro\venv\lib\site-packages\flask_restless\views.py", line 1239, in get
return self._search()
File "C:\Users\user\PycharmProjects\repro\venv\lib\site-packages\flask_restless\views.py", line 1194, in _search
result = self._paginated(result, deep)
File "C:\Users\user\PycharmProjects\repro\venv\lib\site-packages\flask_restless\views.py", line 981, in _paginated
for x in instances[start:end]]
File "C:\Users\user\PycharmProjects\repro\venv\lib\site-packages\sqlalchemy\orm\query.py", line 2547, in __getitem__
return list(res)
File "C:\Users\user\PycharmProjects\repro\venv\lib\site-packages\sqlalchemy\orm\query.py", line 2855, in __iter__
return self._execute_and_instances(context)
File "C:\Users\user\PycharmProjects\repro\venv\lib\site-packages\sqlalchemy\orm\query.py", line 2878, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File "C:\Users\user\PycharmProjects\repro\venv\lib\site-packages\sqlalchemy\engine\base.py", line 945, in execute
return meth(self, multiparams, params)
File "C:\Users\user\PycharmProjects\repro\venv\lib\site-packages\sqlalchemy\sql\elements.py", line 263, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "C:\Users\user\PycharmProjects\repro\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1053, in _execute_clauseelement
compiled_sql, distilled_params
File "C:\Users\user\PycharmProjects\repro\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1189, in _execute_context
context)
File "C:\Users\user\PycharmProjects\repro\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1402, in _handle_dbapi_exception
exc_info
File "C:\Users\user\PycharmProjects\repro\venv\lib\site-packages\sqlalchemy\util\compat.py", line 203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "C:\Users\user\PycharmProjects\repro\venv\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise
raise value.with_traceback(tb)
File "C:\Users\user\PycharmProjects\repro\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1182, in _execute_context
context)
File "C:\Users\user\PycharmProjects\repro\venv\lib\site-packages\sqlalchemy\engine\default.py", line 470, in do_execute
cursor.execute(statement, parameters)