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.
On Dec 21, 10:57 pm, mdipierro <[email protected]> wrote:
> 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);
>
> On Dec 21, 2:55 pm, HaM <[email protected]> wrote:
>
> > I just tried with the last revision (1414:da25156addab) and the
> > problem stills the same.
>
>
