group_by with subset of columns doesn't work with postgres backend

[tiagob]

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.

[tiagob]

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!

[jfinkels]

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!)

[swifmaneum]

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:mysecretpassword@192.168.99.100: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)