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.
On 24 déc, 02:57, mdipierro <[email protected]> wrote:
> 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.
>
> On Dec 22, 2:52 am, HaM <[email protected]> wrote:
>
> > 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.
>
>
