Double JOIN in GroupByChartView when using relations in group and base_filters

[wWolfovich]

Environment

Flask-Appbuilder version: 2.1.9

pip freeze output: apispec==1.3.3 attrs==19.1.0 Babel==2.7.0 blinker==1.4 Click==7.0 colorama==0.4.1 defusedxml==0.6.0 Flask==1.1.1 Flask-AppBuilder==2.1.9 Flask-Babel==0.12.2 Flask-JWT-Extended==3.21.0 Flask-LDAP==0.1.6 Flask-Login==0.4.1 Flask-Mail==0.9.1 Flask-OpenID==1.2.5 flask-redis==0.4.0 Flask-SQLAlchemy==2.4.0 Flask-WTF==0.14.2 gevent==1.4.0 greenlet==0.4.15 gunicorn==19.9.0 huey==2.1.1 itsdangerous==1.1.0 Jinja2==2.10.1 jsonschema==3.0.2 MarkupSafe==1.1.1 marshmallow==2.19.5 marshmallow-enum==1.4.1 marshmallow-sqlalchemy==0.17.0 mysqlclient==1.4.2.post1 pkg-resources==0.0.0 prison==0.1.2 psycopg2==2.8.3 pyasn1==0.4.6 pyasn1-modules==0.2.6 PyJWT==1.7.1 pyrsistent==0.15.4 python-dateutil==2.8.0 python-ldap==3.2.0 python3-openid==3.1.0 pytz==2019.2 PyYAML==5.1.2 redis==3.3.7 six==1.12.0 SQLAlchemy==1.3.6 SQLAlchemy-Utils==0.34.1 Werkzeug==0.15.5 WTForms==2.2.1

Expected results

I want to build GroupByChartView filtered out by to fields which are FKs and want grouped field be labeled by row from related model.

My models are

class ClubSis(Model):
    __bind_key__ = armsis
    __tablename__ = "club_sis"
    id = Column(Integer, primary_key=True)
    uid = Column(String(255))
    name = Column(String(255))
    status = Column(String(255))

    def __repr__(self):
        return "{} ({})".format(self.name, self.uid)


class Proxy(Model):
    __bind_key__ = armsis
    __tablename__ = "proxy"
    id = Column(Integer, primary_key=True)
    comment = Column(String(255))

    def __repr__(self):
        return self.comment


class ProxyToClub(Model):
    __bind_key__ = armsis
    __tablename__ = "proxy_to_club"
    proxy_id = Column(Integer,
                      ForeignKey('proxy.id'),
                      primary_key=True,
                      nullable=False)
    proxy = relationship("Proxy")
    club_id = Column(Integer,
                     ForeignKey('club_sis.id'),
                     primary_key=True,
                     nullable=False)
    club = relationship("ClubSis")

    def __repr__(self):
        return "{}-{}".format(self.proxy, self.club)

Actual results

Defining group by "virtual" field name which is relationship itself (like in docs example) doesn't work and throws exception:

Traceback (most recent call last):
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/flask/app.py", line 2446, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/flask/app.py", line 1951, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/flask/app.py", line 1820, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/flask/app.py", line 1949, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/flask/app.py", line 1935, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/flask_appbuilder/security/decorators.py", line 123, in wraps
    return f(self, *args, **kwargs)
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/flask_appbuilder/charts/views.py", line 205, in chart
    order_direction="asc",
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/flask_appbuilder/charts/views.py", line 180, in _get_chart_widget
    group.apply(lst, sort=order_column == ""), self.label_columns
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/flask_appbuilder/models/group.py", line 343, in apply
    data = sorted(data, key=self.attrgetter(*self.group_bys_cols))
TypeError: '<' not supported between instances of 'Proxy' and 'Proxy'

But I realized that it works if I use

definitions = [{
...
"group": "proxy.comment"
...
}]

But with it I can't use base_filters by "proxy.comment" field, because it generates double JOIN to proxy table.

class ClubsPerNodeView(GroupByChartView):
    datamodel = SQLAInterface(ProxyToClub)
    chart_title = "Clubs per proxy"

    definitions = [
        {
            "label": "clubs/proxy",
            "group": "proxy.comment",
            "series": [(aggregate_count, "club_id")]
        }
    ]
    base_filters = [
        ["club.status", FilterEqual, "IN_WORK"],
        ["proxy.comment", FilterContains, "Метео"]
    ]

Traceback (most recent call last):
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
    cursor, statement, parameters, context
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.DuplicateAlias: table name "proxy" specified more than once


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/flask/app.py", line 2446, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/flask/app.py", line 1951, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/flask/app.py", line 1820, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/flask/app.py", line 1949, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/flask/app.py", line 1935, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/flask_appbuilder/security/decorators.py", line 123, in wraps
    return f(self, *args, **kwargs)
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/flask_appbuilder/charts/views.py", line 205, in chart
    order_direction="asc",
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/flask_appbuilder/charts/views.py", line 174, in _get_chart_widget
    order_direction=order_direction,
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/flask_appbuilder/models/sqla/interface.py", line 189, in query
    return count, query.all()
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3174, in all
    return list(self)
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3330, in __iter__
    return self._execute_and_instances(context)
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3355, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
    distilled_params,
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    e, statement, parameters, cursor, context
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
    cursor, statement, parameters, context
  File "/home/wwolf/giphub_env/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateAlias) table name "proxy" specified more than once

[SQL: SELECT proxy_to_club.proxy_id AS proxy_to_club_proxy_id, proxy_to_club.club_id AS proxy_to_club_club_id 
FROM proxy_to_club LEFT OUTER JOIN proxy ON proxy.id = proxy_to_club.proxy_id JOIN club_sis ON club_sis.id = proxy_to_club.club_id JOIN proxy ON proxy.id = proxy_to_club.proxy_id 
WHERE club_sis.status = %(status_1)s AND proxy.comment LIKE %(comment_1)s ORDER BY proxy.comment ASC]
[parameters: {'status_1': 'IN_WORK', 'comment_1': '%Метео%'}]
(Background on this error at: http://sqlalche.me/e/f405)

Steps to reproduce

Use relations in group and base_filters properties of GroupByChartView at the same time.