Re: [eclipselink-users] Conditional LEFT JOIN in JPQL

[Tom Ware <tom.ware@xxxxxxxxxx>]

Hi Bernard,

  Since this is a spec issue, feedback to the spec group is likely the best 
long term solution.

  In the short to medium term, it is a matter of determining what EclipseLink 
can do for you.  For our upcoming 2.1 release we have been adding some 
enhancements to the way queries can be run.  (downcasting and a function 
operator in JPQL so far and some fetch group support through hints and 
configuration options), so changes to the way queries work are not out of the 
question.  We are, however, somewhat limited in the degree to which we can 
change JPQL - i.e. we have to remain TCK compliant and we do not want to add 
JPQL that will be disallowed by later specifications. As a result we have been 
hesitant to address issues in JPQL that require major grammar rewrites.

  Have you looked at the criteria API?  I haven't had a chance to investigate 
how one would write this kind of query in the criteria API, but it is, in some 
ways more flexible that JPQL especially with regards to how joins are handled.

  Assuming that is of no help, perhaps your best bet is EclipseLink native API. 
 Is that an option for you - since you are asking for a non-spec feature anyway?

-Tom

bht@xxxxxxxxxxxxx wrote:
> Hi Chris,
>
> Thanks for that. It is amazing how many incident records the community
> had to keep track of with regard to this single issue. I wasn't aware
> of this one.
>
> You did not mention the JPQL issue. Where is this tracked? As I wrote,
> I prefer to stay clear of bad practices in standard use cases such as
> this common outer join application.
>
> This seems to be a bigger one as it is on the specification level,
> where EL would not have a compliance issue. Is that why you do not
> mention it?
>
> Many thanks.
>
> Regards
>
> Bernard
>
> On Mon, 03 May 2010 15:13:10 -0400, you wrote:
>
> > Hello Bernard,
> >
> > With respect to case and native queries, you will find that EclipseLink 
> > has worked around the problem of databases returning different cases 
> > through bug 299926
> > https://bugs.eclipse.org/bugs/show_bug.cgi?id=299926
> > If using a recent EclipseLink nightly build, you should not see case 
> > issues when using native SQL (unless your database is case sensitive). 
> >
> > Best Regards,
> > Chris
> >
> > On 03/05/2010 3:03 PM, bht@xxxxxxxxxxxxx wrote:
> > > Hi Vide,
> > >
> > > Re 1)
> > >
> > > It should be possible.
> > >
> > > However there are major limitations with conditional left join in JPQL
> > > and this is a BIG problem.
> > >
> > > For example, in a product list display, alongside with each list item,
> > > we would want an indicator showing whether the item exists in the
> > > user's list of favorite items. An outer join clause with such a
> > > condition defining the join is the only practical way to do this. This
> > > is industry standard, except it is NOT supported in JPQL.
> > >
> > > In JPQL, You cannot code outer join "ON" something other than what is
> > > already obvious from relationships. In fact "ON" is not supported.
> > >
> > > In SQL you can say:
> > >
> > > SELECT d.name, count( v.id ) FROM dealer d LEFT OUTER JOIN vehicle v
> > > ON v.dealer_id = d.dealer_id AND v.type = 'New' GROUP BY d.name
> > >
> > > In JPQL, you only have the WHERE clause which limits your results,
> > > making the outer join useless:
> > >
> > > SELECT d.name, count( v.id ) FROM dealer d LEFT OUTER JOIN
> > > d.vehicleList v WHERE v.type = 'New' GROUP BY d.name
> > >
> > >
> > > PLease refer to:
> > >
> > > http://old.nabble.com/LEFT-OUTER-JOIN---ON-clause-equivalent--td12638986.html#a12641034
> > >
> > > One can work around this with a native query, but this is a really
> > > lame suggestion because there are a number of problems with them -
> > > they are not best practice. I found them very difficult to work with
> > > in this scenario because of unexpected case sensitivity issues in the
> > > query itself (not the data) when using @SqlResultSetMapping. You will
> > > discover the whole glory when you get there:
> > >
> > > http://forums.java.net/jive/thread.jspa?messageID=190092
> > >     Request for input - TLE case sensitivity
> > >     Outlines workarounds
> > > https://glassfish.dev.java.net/issues/show_bug.cgi?id=3698
> > >     Details workaround
> > > https://glassfish.dev.java.net/issues/show_bug.cgi?id=600
> > > https://glassfish.dev.java.net/issues/show_bug.cgi?id=1402
> > > https://glassfish.dev.java.net/issues/show_bug.cgi?id=1442
> > > https://glassfish.dev.java.net/issues/show_bug.cgi?id=1507
> > > https://glassfish.dev.java.net/issues/show_bug.cgi?id=2046
> > > https://glassfish.dev.java.net/issues/show_bug.cgi?id=887
> > > https://glassfish.dev.java.net/issues/show_bug.cgi?id=832
> > > https://glassfish.dev.java.net/issues/show_bug.cgi?id=1027
> > >
> > > Unless fields are wrapped in quotes,
> > >  Oracle returns fields in uppercase,
> > >  Sybase returns them as they were defined,
> > >  and Postgres returns them in lowercase.
> > >
> > >
> > >  http://sql-info.de/en/postgresql/postgres-gotchas.html#1_2
> > >      Unquoted object names fold to lower case (folding behavior)
> > >
> > >
> > >
> > > Regards
> > >
> > > Bernard
> > >
> > >
> > >
> > > On Mon, 3 May 2010 01:39:31 -0700 (PDT), you wrote:
> > >
> > >   
> > > > Hello,
> > > >
> > > >  I have two questions about JOIN's in JPQL:
> > > > 1. Is it possible to write a conditional LEFT JOIN with JPQL, which in SQL
> > > > looks like:
> > > >
> > > > SELECT a.*, b.*, c.*
> > > >  FROM entityA a
> > > >  LEFT JOIN entityB b ON a.id=b.a_id AND b.some_field="some_value"
> > > >  LEFT JOIN entityC c ON a.id=c.a_id AND c.some_field="some_value"
> > > >  WHERE some_other_post_conditions
> > > >
> > > > 2. Can I use LEFT JOIN's in JPQL on entities, which don't have relationship
> > > > annotations between each other?
> > > >
> > > > Thanks
> > > >     
> > > _______________________________________________
> > > eclipselink-users mailing list
> > > eclipselink-users@xxxxxxxxxxx
> > > https://dev.eclipse.org/mailman/listinfo/eclipselink-users
> > >   
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users