Re: how to alias target table of multiple inner joins

[Richard PALO]

Le 05/09/2017 à 15:14, Richard PALO a écrit :
> any hint on how to do this?
>
noticing an interesting example in the python-sql overview,
I tried the following and it seems to hack it out:
> account_move = Table('account_move')
> account_journal = Table('account_journal')
> ir_sequence = Table('ir_sequence')
>
> j1 = account_journal.join(
> account_move,
> condition=account_move.journal_id == account_journal.id)
>
> j2 = j1.join(
> ir_sequence,
> condition=ir_sequence.id == account_journal.sequence_id)
>
> j3 = j2.join(Table('account_account'))
> j3.condition = j3.right.id == account_journal.default_debit_account_id
>
> j4 = j3.join(Table('account_account'))
> j4.condition = j4.right.id == account_journal.default_credit_account_id
>
> query = j4.select(
> account_journal.id, account_journal.code, account_journal.name,
> account_journal.type,
> ir_sequence.name.as_('sequence_name'),
> j3.right.code.as_('default_debit_account'),
> j4.right.code.as_('default_credit_account'),
> distinct_on=account_journal.id)
>
> print(query)
> cur.execute(*query)
>
> for jnl in cur:
> print(jnl.id, jnl.code, jnl.name, jnl.type, jnl.sequence_name,
> jnl.default_debit_account, jnl.default_credit_account)
>
>

The print statement pretty much shows the query I intended though what I don't
get now is why python-sql needs the 'distinct_on' clause to avoid excessive output...
that is, neither psql nor phppgadmin sql need it to work correctly.

Is this an anomalie that perhaps should have an issue filed?

--

Richard PALO

[Richard PALO]

Scratching my head on how one would be able to do the following simplified
psql snippet in python-sql (where account_account is targetted twice):
> select j.id, j.code, j.name, j.type, s.name, dda.code, dca.code
> from account_journal j
> join ir_sequence s on s.id = j.sequence_id
> join account_account dda on dda.id = j.default_debit_account_id
> join account_account dca on dca.id = j.default_credit_account_id

If I try a single Table() definition, the following:
> account_account = Table('account_move')
> #dda = Table('account_move')
> #dca = Table('account_move')

> account_move = Table('account_move')
> account_journal = Table('account_journal')
> ir_sequence = Table('ir_sequence')
>

> query = account_journal.join(
> account_move,
> condition=account_move.journal_id == account_journal.id)
>
> query = query.join(
> ir_sequence,
> condition=ir_sequence.id == account_journal.sequence_id)
>
> query = query.join(
> account_account.as_('dda'),
> condition=dda.id == account_journal.default_debit_account_id)
>
> query = query.join(
> account_account.as_('dca'),
> condition=dca.id == account_journal.default_credit_account_id)
>
> query = query.select(

> account_journal.id, account_journal.code, account_journal.name,
> account_journal.type, ir_sequence.name.as_('sequence_name'),

> dda.code.as_('default_debit_account'),
> dca.code.as_('default_credit_account'))
>
> cur.execute(*query)
>

I get
> account_account.as_('dda'),
> TypeError: 'Column' object is not callable
>

If I try the style with two differently named tables(and the joined
table names appropriately updated, I get:
> cur.execute(*query)
> File "/usr/lib/python3.6/site-packages/psycopg2/extras.py", line 316, in execu
> te
> return super(NamedTupleCursor, self).execute(query, vars)
> psycopg2.ProgrammingError: ERREUR: la colonne d.code n'existe pas
> LINE 1: ..., "a"."code", "a"."name", "a"."type", "c"."name", "d"."code"...
> ^
> HINT: Peut-être que vous souhaitiez référencer la colonne « a.code » ou la colo
> nne « c.code ».

>

any hint on how to do this?

--

Richard PALO

[Cédric Krier]

On 2017-09-05 16:52, Richard PALO wrote:
> Le 05/09/2017 à 15:14, Richard PALO a écrit :
> > any hint on how to do this?
> >
> noticing an interesting example in the python-sql overview,
> I tried the following and it seems to hack it out:
> > account_move = Table('account_move')
> > account_journal = Table('account_journal')
> > ir_sequence = Table('ir_sequence')
> >
> > j1 = account_journal.join(
> > account_move,
> > condition=account_move.journal_id == account_journal.id)
> >
> > j2 = j1.join(
> > ir_sequence,
> > condition=ir_sequence.id == account_journal.sequence_id)
> >
> > j3 = j2.join(Table('account_account'))
> > j3.condition = j3.right.id == account_journal.default_debit_account_id

You should write instead:

account_account1 = Table('account_account')

j3 = j2.join(account_account1,
condition=account_account1.id ==
account_journal.default_debit_account_id)

> > j4 = j3.join(Table('account_account'))
> > j4.condition = j4.right.id == account_journal.default_credit_account_id

Idem, it is better to not rely on the right/left parameter.

> > query = j4.select(
> > account_journal.id, account_journal.code, account_journal.name,
> > account_journal.type,
> > ir_sequence.name.as_('sequence_name'),
> > j3.right.code.as_('default_debit_account'),
> > j4.right.code.as_('default_credit_account'),
> > distinct_on=account_journal.id)
> >
> > print(query)
> > cur.execute(*query)
> >
> > for jnl in cur:
> > print(jnl.id, jnl.code, jnl.name, jnl.type, jnl.sequence_name,
> > jnl.default_debit_account, jnl.default_credit_account)
> >
> >
>
> The print statement pretty much shows the query I intended though what I don't
> get now is why python-sql needs the 'distinct_on' clause to avoid excessive output...
> that is, neither psql nor phppgadmin sql need it to work correctly.
>
> Is this an anomalie that perhaps should have an issue filed?

It is difficult to say it without seeing the executed SQL.

--
Cédric Krier - B2CK SPRL
Email/Jabber: cedric...@b2ck.com
Tel: +32 472 54 46 59
Website: http://www.b2ck.com/

[Richard PALO]

Le 06/09/2017 à 18:13, Cédric Krier a écrit :
>> The print statement pretty much shows the query I intended though what I don't
>> get now is why python-sql needs the 'distinct_on' clause to avoid excessive output...
>> that is, neither psql nor phppgadmin sql need it to work correctly.
>>
>> Is this an anomalie that perhaps should have an issue filed?
>
> It is difficult to say it without seeing the executed SQL.
>

This was due to evolution of my test program and the addition of account_move in order
to filter out the bogus builtin journals from openerp (which seem to exist as well in tryton).
THe distinct is thus correctly needed. sorry for the noise.

Do I understand therefore that aliasing is indeed not supported on tables (that is, only on columns)?

cheers,
--

Richard PALO

[Cédric Krier]

On 2017-09-07 17:08, Richard PALO wrote:
> Do I understand therefore that aliasing is indeed not supported on tables (that is, only on columns)?

alias on table is pointless in python-sql because query is written using
Python variable which has the same purpose.