The as_row recipe worked. Thanks for the quick response, Mike.
On Thursday, November 17, 2016 at 7:25:07 AM UTC-5, Darin Gordon wrote:
>
> Using: postgres 9.6 and latest sqlalchemy 1.1.4
>
> I've been trying to port a query to a sqlalchemy query but have gotten an
> exception about an unrecognized keyword arg, which
> confuses me as to whether the issue is my code or a bug in sqlalchemy:
> TypeError: self_group() got an unexpected keyword argument 'against'
>
>
>
> I've been trying to port the following query, which works in psql, to a
> sqlalchemy query:
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> select domain, json_agg(parts) as permissions from
> (select domain, row_to_json(r) as parts from
> (select domain, action, array_agg(distinct target) as
> target from
> (select (case when domain is null then '*' else
> domain end) as domain,
> (case when target is null then '*' else
> target end) as target,
> array_agg(distinct (case when action is
> null then '*' else action end)) as action
> from permission
> group by domain, target
> ) x
> group by domain, action)
> r) parts
> group by domain;
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> The following sqlalchemy query raises the exception:
>
> thedomain = case([(Domain.name == None, '*')], else_=Domain.name)
> theaction = case([(Action.name == None, '*')], else_=Action.name)
> theresource = case([(Resource.name == None, '*')],
> else_=Resource.name)
>
> action_agg = func.array_agg(theaction.distinct())
>
> stmt1 = (
> session.query(thedomain.label('domain'),
> theresource.label('resource'),
> action_agg.label('action')).
> select_from(User).
> join(role_membership_table, User.pk_id ==
> role_membership_table.c.user_id).
> join(role_permission_table, role_membership_table.c.role_id ==
> role_permission_table.c.role_id).
> join(Permission, role_permission_table.c.permission_id ==
> Permission.pk_id).
> outerjoin(Domain, Permission.domain_id == Domain.pk_id).
> outerjoin(Action, Permission.action_id == Action.pk_id).
> outerjoin(Resource, Permission.resource_id == Resource.pk_id).
> filter(User.identifier == identifier).
> group_by(Permission.domain_id,
> Permission.resource_id)).subquery()
>
> stmt2 = (session.query(stmt1.c.domain,
> stmt1.c.action,
>
> func.array_agg(stmt1.c.resource.distinct())).
> select_from(stmt1).
> group_by(stmt1.c.domain, stmt1.c.action)).subquery()
>
> stmt3 = (session.query(stmt2.c.domain,
> func.row_to_json(stmt2)).
> select_from(stmt2)).subquery()
>
> final = (session.query(stmt3.c.domain, func.json_agg(stmt3)).
> select_from(stmt3).
> group_by(stmt3.c.domain))
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Here's the exception trace:
>
> ...../yosai_alchemystore/accountstore/accountstore.py in
> _get_indexed_permissions_query(self, session, identifier)
> 156
> 157 stmt3 = (session.query(stmt2.c.domain,
> --> 158 func.row_to_json(stmt2)).
> 159 select_from(stmt2)).subquery()
> 160
>
> ...../lib/python3.5/site-packages/sqlalchemy/sql/functions.py in
> __call__(self, *c, **kwargs)
> 322
> 323 return Function(self.__names[-1],
> --> 324 packagenames=self.__names[0:-1], *c, **o)
> 325
> 326
>
> ...../lib/python3.5/site-packages/sqlalchemy/sql/functions.py in
> __init__(self, name, *clauses, **kw)
> 432 self.type = sqltypes.to_instance(kw.get('type_', None))
> 433
> --> 434 FunctionElement.__init__(self, *clauses, **kw)
> 435
> 436 def _bind_param(self, operator, obj, type_=None):
>
> ...../lib/python3.5/site-packages/sqlalchemy/sql/functions.py in
> __init__(self, *clauses, **kwargs)
> 60 self.clause_expr = ClauseList(
> 61 operator=operators.comma_op,
> ---> 62 group_contents=True, *args).\
> 63 self_group()
> 64
>
> ...../lib/python3.5/site-packages/sqlalchemy/sql/elements.py in
> __init__(self, *clauses, **kwargs)
> 1783 self.clauses = [
> 1784
> text_converter(clause).self_group(against=self.operator)
> -> 1785 for clause in clauses]
> 1786 else:
> 1787 self.clauses = [
>
> ...../lib/python3.5/site-packages/sqlalchemy/sql/elements.py in
> <listcomp>(.0)
> 1783 self.clauses = [
> 1784
> text_converter(clause).self_group(against=self.operator)
> -> 1785 for clause in clauses]
> 1786 else:
> 1787 self.clauses = [
>
> TypeError: self_group() got an unexpected keyword argument 'against'
>
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
