Re: [python-sql] arrays and json with python-sql

Richard PALO Fri, 08 Sep 2017 02:28:02 -0700

Le 07/09/2017 à 19:12, Cédric Krier a écrit :

ARRAY_AGG does not seem to be in any SQL standard, so it is not
implemented in python-sql. But it is very simple to create it:


     from sql.aggregate import Aggregate

     class ArrayAgg(Aggregate):
         __slots__ = ()
         _sql = 'ARRAY_AGG'

Okay, super, I believe I got much further with this.

class JsonAgg(Aggregate):
    __slots__ = ()
    _sql = 'json_agg'

fy = Table('account_fiscalyear')
j = Table('account_journal')
s = Table('ir_sequence')
s_fy = Table('account_sequence_fiscalyear')

subquery = j.join(
    s_fy,
    condition=s_fy.fiscalyear_id == fy.id)
subquery = subquery.join(
    s,
    condition=s.id == s_fy.sequence_id)
subquery = subquery.select(
    j.type, s.name, s.number_next, s.padding, s.prefix,
    where=(j.sequence_id == s_fy.sequence_main_id) &
           (j.type.in_(['sale', 'sale_refund', 'purchase', 'purchase_refund'])))

x = With(query=subquery)
invoice_sequences = x.select(As(JsonAgg(x), 'invoice_sequences'), with_=[x])

query = fy.select(

fy.id, fy.name, fy.date_start, fy.date_stop, invoice_sequences,order_by=fy.id.asc)


print(query)
cur.execute(*query)

for x in cur:
    print(x.id, x.name, x.date_start, x.date_stop, x.invoice_sequences)


The resulting (reformatted) query is:

SELECT "a"."id", "a"."name", "a"."date_start", "a"."date_stop",( WITH "b" AS (SELECT "c"."type", "e"."name", "e"."number_next", "e"."padding", "e"."prefix"FROM "account_journal" AS "c"INNER JOIN "account_sequence_fiscalyear" AS "d" ON ("d"."fiscalyear_id" = "a"."id")INNER JOIN "ir_sequence" AS "e" ON ("e"."id" = "d"."sequence_id")WHERE (("c"."sequence_id" = "d"."sequence_main_id")AND ("c"."type" IN (%s, %s, %s, %s))))SELECT json_agg("b") AS "invoice_sequences" FROM "b" AS "b")FROM "account_fiscalyear" AS "a"ORDER BY "a"."id" ASC


which is what, after a slight rework of the initial psql, seemed the most 
logical solution at this point

This is the psql:

select fy.id, fy.name, fy.date_start, fy.date_stop,( with x as (select j.type, s.name, s.number_next, s.padding, s.prefix
          from account_journal j
          join account_sequence_fiscalyear s_fy on s_fy.fiscalyear_id = fy.id
          join ir_sequence s on s.id = s_fy.sequence_id
where j.sequence_id = s_fy.sequence_main_idand j.type in ('sale', 'sale_refund', 'purchase', 'purchase_refund')
         )
      select json_agg(x.*) as "invoice_sequences" from x)
from account_fiscalyear fy
order by fy.id asc


I should now be able to get the json aggregate into a dictionary list.

Cheers

--

Richard PALO

--
You received this message because you are subscribed to the Google Groups 
"python-sql" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to [email protected].
For more options, visit https://groups.google.com/d/optout.

Re: [python-sql] arrays and json with python-sql

Reply via email to