|Re: [eclipselink-users] Conditional LEFT JOIN in JPQL
Hi Bernard,If forwarded your suggestion some members of the spec group. The thing that immediately came up was:
Is there a compelling reason to have a foreign key relationship between two entities and no mapping for that relationship defined? If so, what is it? In order to get much traction in the JPA group, a good argument will have to be made in that regard.
As far as EclipseLInk is concerned, there are a couple of ways we could support this without JPQL changes.
1. I believe our native API will support this now. If it is useful to you, I can put together some code to demonstrate how to take a JPQL query and alter it with our Native API to do this.
2. EclipseLink allows you to define something called a query key. A query key allows you to simulate an attribute on an object without actually mapping it. We have been discussing enhancing our JPQL support to check for query keys as well as mappings. If we did that, you could use our customization API to add a query key and then use JPQL as if the relationship existed. The first step is to file an enhancement asking for query keys to be supported through JPQL and then, with enough votes, it would get to the top of our list fairly soon - assuming this is as common a problem as you say, that should be possible.
-Tom bht@xxxxxxxxxxxxx wrote:
Hi Tom, Thanks for your helpful reply, and I mean, really helpful and considerate. But I think it is fair to say that this stuff is just to simplistic on the JPQL side and as a consequence of that, JPA overall is too difficult to use. Even simple things such as this come at the cost of becoming research projects with administration overhead of release and bug tracking, lobbying in specification groups and so on. Where do we find THIS kind of developmet resources? I think you might agree with me that this is too much for the average development shop, and I woud appreciate if Oracle would take the much needed initiative in this area. Vidas has found a way out of this with native queries as I had found many months ago, and if he is lucky, then there are no case sensitivity problems with @SqlResultSetMapping. But still, one cannot use select * in native queries if multiple tables are involved as in Vidas's case because of duplicate column names such as "id", "name" in the tables. Which means that to populate domain objects one must list every field of every table. It is a maintenance nightmare, and one gets further and further away from the original idea of JPA which is to make life easier not more difficult than with JDBC and DAOs. I just built hashtables in the application to filter the resultsets as a workaround for the condition in the outer join because the workarounds were just too costly. Still messy, lacking performance and very annoying but at least refactor safe. JPQL is obviously key to all this, and there hasn't been any improvement in JPA 2.0, so I am very disappointed, and I want you guys to be aware of it. JPQL is quite useless except for the most basic queries, but who writes such basic queries these days? Thanks again, Bernard On Mon, 03 May 2010 16:15:30 -0400, you wrote: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 299926Best 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_______________________________________________ eclipselink-users mailing list eclipselink-users@xxxxxxxxxxx https://dev.eclipse.org/mailman/listinfo/eclipselink-users
Back to the top