Eclipse Community Forums: EclipseLink » How to access the object created in a select clause with constructor in an order by?

Home » Eclipse Projects » EclipseLink » How to access the object created in a select clause with constructor in an order by?(JPQL)

Show: Today's Messages :: Show Polls :: Message Navigator

How to access the object created in a select clause with constructor in an order by? [message #520639]

Sat, 13 March 2010 16:10

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?

Report message to a moderator

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

Chris Delahunt

Messages: 1389
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

Report message to a moderator

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

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.

Report message to a moderator

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

Chris Delahunt

Messages: 1389
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

Report message to a moderator

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

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?

Report message to a moderator