This should now be fixed in trunk. Please confirm.
On Dec 27, 3:23 am, HaM <yarib...@gmail.com> wrote:
> 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 <mdipie...@cs.depaul.edu> 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 <yarib...@gmail.com> 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 <mdipie...@cs.depaul.edu> 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 <yarib...@gmail.com> wrote:
>
> > > > > I just tried with the last revision (1414:da25156addab) and the
> > > > > problem stills the same.
>
>
