Left Join with aliased table and the new DAL

[HaM]

Hi everyone,

I currently use Web2py for an internal corporation application. I
recently upgraded from 1.89 to 1.90-6. Since this upgrade all my left
joins with aliased table don't work any more.
I'm using Web2py with Postgres.

For example I have this in a controller exposed function:
Domain = dbPsnol.domain
Client = dbPsnol.client
Manager = dbPsnol.contact.with_alias('manager')

infos = dbPsnol(Domain.id==request.args[0]).select(
Domain.ALL, Client.ALL,Manager.ALL, left=[
Client.on(Client.id==Domain.client_id),
Manager.on(Manager.id==Client.manager_id)])

This example generate the following error:
ProgrammingError: invalid reference to FROM-clause entry for table
"domain"
LINE 1: ... domain, contact LEFT JOIN client ON (client.id =
domain.cli...
^
HINT: There is an entry for table "domain", but it cannot be
referenced from this part of the query.

And as soon as I redefine Manager like this:
Manager = dbPsnol.contact
I don't get the error any more. But the result is not what I want.

Is this a bug in the new DAL or is there an other way to do what I
want ?

[mdipierro]

Thanks for reporting this. It is definitively a bug and I will try fix
it today.

[mdipierro]

I think this is now fixed in trunk. Can you give it a try?

Massimo

On Dec 21, 9:25 am, HaM <yarib...@gmail.com> wrote:

[HaM]

I just tried with the last revision (1414:da25156addab) and the
problem stills the same.

[mdipierro]

hmm...

I am trying your select and it now generates the same sql as the old
dal. please try this:

Domain = db.domain
Client = db.client
Manager = db.contact.with_alias('manager')
sql = db(Domain.id==1)._select(

Domain.ALL, Client.ALL,Manager.ALL,
left=[Client.on(Client.id==Domain.client_id),
Manager.on(Manager.id==Client.manager_id)])

print sql

what do you get?
I get

SELECT domain.id, domain.name, domain.client_id, client.id,
client.name, client.manager_id, manager.id, manager.name FROM domain
LEFT JOIN client ON (client.id = domain.client_id) LEFT JOIN contact
AS manager ON (manager.id = client.manager_id) WHERE (domain.id = 1);

[HaM]

Ok in order to simplify the problem I have changed my code to this:

Domain = db.domain
Client = db.client
Manager = db.contact.with_alias('manager')

sql = db((Domain.id==1)&(Client.id==Domain.client_id))._select(
Domain.name, Client.name, Manager.name,
left=[Manager.on(Manager.id==Client.manager_id)])
print sql

The result is:
SELECT domain.name, client.name, contact.name FROM domain, client,
contact LEFT JOIN contact AS manager ON (contact.id =
client.manager_id) WHERE ((domain.id = 1) AND (client.id =
domain.client_id));

And this request is not correct for PostgreSQL. I think that it should
be :
SELECT domain.name, client.name, manager.name FROM domain, client
LEFT JOIN contact as manager ON (manager.id = client.manager_id) WHERE
((domain.id = 1) AND (client.id = domain.client_id));

Which works well.

In order to push research further I also tried to do only INNER JOIN
with an aliased table and it partially works:

Domain = db.domain
Client = db.client
Manager = db.contact.with_alias('manager')

sql = db((Domain.id==1)&
(Client.id==Domain.client_id)&
(Manager.id==Client.manager_id))._select(
Domain.name, Client.name, Manager.name)
print sql

Result:
SELECT domain.name, client.name, contact.name FROM domain, client,
contact WHERE (((domain.id = 1) AND (client.id = domain.client_id))
AND (contact.id = client.manager_id));

It works but it doesn't use the alias name for the table contact. Thus
the resulting dict() doesn't contains the key "manager" but the key
"contact".

Thank you for investigating this problem so quickly.

[mdipierro]

I took a second look. The first problem is indeed fixed. The second
problem is not a new dal issue. It is just that aliased tables in
INNER JOINs never worked well. I will continue to look into it. This
may take a while so could you open a ticket on google code? Thanks.

[HaM]

First, Merry Christmas.

Since I still experiencing problem on left join I have looked more
deeply and try to find where is the difference between old and new
DAL.
I found that my problem only appears on a more complex request:

Domain = dbPsnol.domain
Client = dbPsnol.client

Contact = dbPsnol.contact

Manager = dbPsnol.contact.with_alias('manager')

Datacenter = dbPsnol.datacenter
PsnVersion = dbPsnol.psn_version
Server = dbPsnol.server

query = dbPsnol((Domain.id==request.args[0])&
(Client.id==Domain.client_id)&
(Datacenter.id==Domain.dc_id)&
(PsnVersion.id==Domain.psn_version_id))

sql = query._select(Domain.name, Client.name,

Manager.name, left=[
Manager.on(Manager.id==Client.manager_id)])
print sql

With the old DAL:
SELECT domain.name, client.name, manager.name FROM datacenter, domain,
psn_version, client LEFT JOIN contact AS manager ON
manager.id=client.manager_id WHERE (((domain.id=211 AND
psn_version.id=domain.psn_version_id) AND datacenter.id=domain.dc_id)
AND client.id=domain.client_id);

With the new DAL:
SELECT domain.name, client.name, manager.name FROM datacenter,
domain, client, psn_version LEFT JOIN contact AS manager ON
(manager.id = client.manager_id) WHERE ((((domain.id = 211) AND
(psn_version.id = domain.psn_version_id)) AND (datacenter.id =
domain.dc_id)) AND (client.id = domain.client_id));

The difference is almost invisible, but tables client and psn_version
have swap in the FROM part of the request. It seems that my problem
come from there since the new DAL syntax isn't correct for PostgreSQL.

For my second problem (INNER JOIN and alias) I will open a new ticket
quickly. Many thanks for your work.

[mdipierro]

We'll fix this. Pleas open a ticket on google code. Thanks for
bringing this up.

Massimo

[mdipierro]

This should now be fixed in trunk. Please confirm.

On Dec 27, 3:23 am, HaM <yarib...@gmail.com> wrote:

[HaM]

It's fixed for me. It works with revision 1454:c4465e21c715

Thanks !