Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » How to access the object created in a select clause with constructor in an order by?(JPQL)
How to access the object created in a select clause with constructor in an order by? [message #520639] Sat, 13 March 2010 16:10 Go to next message
Daniel Le Berre is currently offline Daniel Le Berre
Messages: 82
Registered: July 2009
Member
I have the following JPQL query that works fine:

"SELECT interest, COUNT(contact) AS nbContacts " +
				"FROM SocialEntity soc, IN(soc.contacts) contact, " +
				"IN(contact.interests) interest " +
				"WHERE soc = :socialEntity AND interest NOT MEMBER OF soc.interests " +
				"GROUP BY interest ORDER BY nbContacts DESC")


The problem is that the result of my query is a list of array of objects.

I would like to create new objects MyResult to have a list of MyResult using a TypedQuery:

"SELECT new path.to.MyResult(interest, COUNT(contact)) AS result " +
				"FROM SocialEntity soc, IN(soc.contacts) contact, " +
				"IN(contact.interests) interest " +
				"WHERE soc = :socialEntity AND interest NOT MEMBER OF soc.interests " +
				"GROUP BY interest ORDER BY result.count DESC",MyResult.class)


Unfortunately, it looks like it is not possible to access result in the ORDER BY:

invalid navigation expression [result.count], cannot navigate expression [result] of type [null] inside a query.


I cannot find in JPA 2.0 spec how to access the objects created in the SELECT clause.

Does anyone know how to do this?
Re: How to access the object created in a select clause with constructor in an order by? [message #520886 is a reply to message #520639] Mon, 15 March 2010 16:27 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1019
Registered: July 2009
Senior Member
Hello,

Looks like a problem with the restriction that group by items must be in the select clause. Please file a bug to have this fixed, as it should allow the group by item to be used within the constructor. Workaround would be to select using the constructor and the 'interest', but this would again start returning a list of array objects - the 'interest' and the MyResult object.

Best Regards,
Chris
Re: How to access the object created in a select clause with constructor in an order by? [message #520924 is a reply to message #520886] Mon, 15 March 2010 19:36 Go to previous messageGo to next message
Daniel Le Berre is currently offline Daniel Le Berre
Messages: 82
Registered: July 2009
Member
I do not think that the problem lies in the GROUP BY but in the ORDER BY.

the result.count expression is in the ORDER BY part of the query.
Re: How to access the object created in a select clause with constructor in an order by? [message #521222 is a reply to message #520924] Tue, 16 March 2010 18:55 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1019
Registered: July 2009
Senior Member
My mistake, I didn't notice the orderby clause and made an assumption.

You cannot access constructor attributes within JPQL as they are not entities and do not have mappings - nothing is known about what result.count refers to.

Best Regards,
Chris
Re: How to access the object created in a select clause with constructor in an order by? [message #521240 is a reply to message #521222] Tue, 16 March 2010 21:10 Go to previous messageGo to next message
Daniel Le Berre is currently offline Daniel Le Berre
Messages: 82
Registered: July 2009
Member
ok, I see.

Since I am really only interested by interest objects, I would have liked to write

"SELECT interest " +
				"FROM SocialEntity soc, IN(soc.contacts) contact, " +
				"IN(contact.interests) interest " +
				"WHERE soc = :socialEntity AND interest NOT MEMBER OF soc.interests " +
				"GROUP BY interest ORDER BY COUNT(contact) DESC")



which is allowed in SQL, but does not seem legal in JPQL.

Is there a specific reason not to allow aggregates in ORDER BY clause?
Re: How to access the object created in a select clause with constructor in an order by? [message #522474 is a reply to message #520639] Mon, 22 March 2010 17:55 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Please log a bug for this. EclipseLink should allow this in JPQL.


James : Wiki : Book : Blog : Twitter
Re: How to access the object created in a select clause with constructor in an order by? [message #522508 is a reply to message #522474] Mon, 22 March 2010 20:42 Go to previous message
Daniel Le Berre is currently offline Daniel Le Berre
Messages: 82
Registered: July 2009
Member
Done.

Bug #306766

https://bugs.eclipse.org/bugs/show_bug.cgi?id=306766
Previous Topic:ValueHolder eats memory
Next Topic:How to handle Oracle Store Procedure call with Oracle Types as input or output using EclipseLink
Goto Forum:
  


Current Time: Tue Sep 16 11:36:50 GMT 2014

Powered by FUDForum. Page generated in 0.07589 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software