Re: gdl2: SQLExpression / Postgresql / left joins...

[David Ayers]

Manuel Guesdon wrote:

> Hi,
>
> I've found a problem handling left/right joins with postgresql: Postgresql 
> doesn't accept syntax like where t0.a *= t1.b
> It accepts this only in FROM clause, like:
>        FROM xx as t0 left join yy as t1 on t0.a=t1.b
> Actually, joins are used when building joinClause and the joinClause is added 
> to where clause. Joins are not processed in
> -tableListWithRootEntity (which build from clause)
>
> One way to work around this will be to subclass -joinExpression and 
> -tableListWithRootEntity in PostgresSQLExpression but
> this mean, IMHO, too much code duplication.
>
> Another solution could be to have a method in SQLExpression like 
> -isJoinClauseInFromClause or
> -isSupportingOuterJoinInWhereClause and call this in EOSQLExpression 
> -joinExpression and -tableListWithRootEntit to know
> where we have to put joins.
>
> Any opinion ?
>
> Dave, as you've written a Postgres adaptor, how did you solved this ?
>
>  
Well as Dave seem to be busy, I'll give you my two cents :-)

It's a matter of deciding whether the Postgres adaptor should be a GDL2 
adaptor or an EOF 3 adaptor.  I wouldn't mind either way, but what ever 
we do, we should document it in the class documentation.

If we go with EOF, then it's clear that the adaptor's SQLExpression must 
override -joinExpression and -tableListWithRootEntity.  I'm not sure 
whether there is too much code duplication there, as they would assemble 
the tableList quite differently *if* there is a join, but can merely 
return the result of calling super if there isn't.  -joinExpression 
would merely return an emty string then.  Or am I missing something?

On the otherhand, I know someone is working on a real implementing 
describeModelWithTableNames: for the adaptor, and it seems from a 
general abstraction standpoint that a lot ot the logic could be factored 
out into the super class.  If we actually relied on that, then we would 
have the GDL2 adaptor again.

This all looks like we may be headed towards an EOAccess-Additions 
subproject for GDL2 that could be compiled on WO 4.5.  Then we would 
have the freedom to extend a we please, and make the Postgres adaptor 
depend on either GDL2 or GDL2 Additions for EOF.  If that's the way we 
want to go, then we (I) can look into prividong the infrastructure for 
it.  Which brings us back to the Header discussion :-)

Cheers,
David