Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Eclipselink and oderby does not work on criteria fetch query
Eclipselink and oderby does not work on criteria fetch query [message #1005079] Thu, 24 January 2013 12:58 Go to next message
Mindaugas Pocius is currently offline Mindaugas Pocius
Messages: 7
Registered: October 2012
Junior Member
How to add groupBy criteria to the code below? Because if I add criteriaQuery.groupBy(from.get(minutis.Preke_.prId)); - I get exactly the same SQL statement (like without groupBy):

CriteriaBuilder cb = MinutisManager.getInstance().getCriteriaBuilder();
CriteriaQuery criteriaQuery = cb.createQuery(minutis.Preke.class);
Root<minutis.Preke> from = criteriaQuery.from(minutis.Preke.class);
from.fetch(minutis.Preke_.tiekejai, JoinType.LEFT).fetch(minutis.PrekeTiekejas_.tiekejas, JoinType.LEFT);
//criteriaQuery.groupBy(from.get(minutis.Preke_.prId));

TypedQuery<minutis.Preke> typedQuery = MinutisManager.getInstance().createQuery(criteriaQuery);
typedQuery.setFirstResult(0);
typedQuery.setMaxResults(100);
typedQuery.getResultList();


criteriaQuery.distinct(true) is not an option for me. Because that command hangs the whole statement and if I use EXPLAIN:
distinct.jpg
If I use GROUP BY on the query, then EXPLAIN is:
groupby.jpg

Images are attached

It give the following SQL statements both times (with and without criteriaQuery.groupBy(from.get(minutis.Preke_.prId));):
SELECT ... FROM preke t1 LEFT OUTER JOIN preke_tiekejas t0 ON (t0.pr_id = t1.pr_id) LEFT OUTER JOIN tiekejas t2 ON (t2.tiek_id = t0.tiek_id) LEFT OUTER JOIN gamintojas t3 ON (t3.gam_id = t1.gam_id) LEFT OUTER JOIN google_compare t4 ON (t4.pr_id = t1.pr_id) LEFT OUTER JOIN grupe t5 ON (t5.pgs_id = t1.pgs_id) LEFT OUTER JOIN preke_kaina t6 ON (t6.pr_id = t1.pr_id) ORDER BY t1.pr_id LIMIT ?, ?
  • Attachment: distinct.jpg
    (Size: 51.59KB, Downloaded 63 times)
  • Attachment: groupby.jpg
    (Size: 48.31KB, Downloaded 67 times)

[Updated on: Thu, 24 January 2013 13:47]

Report message to a moderator

Re: Eclipselink and oderby does not work on criteria fetch query [message #1005106 is a reply to message #1005079] Thu, 24 January 2013 13:43 Go to previous message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1035
Registered: July 2009
Senior Member
This is also posted here http://stackoverflow.com/questions/14501589/eclipselink-group-by-is-not-working

Please turn EclipseLink logging on and show the SQL that it generates, and what you are after. The best solution will probably be to just remove the fetch join.
Previous Topic:get sequence number through Eclipselink
Next Topic:Related sub entities not loaded - EAGER ignored?
Goto Forum:
  


Current Time: Thu Oct 30 16:02:10 GMT 2014

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

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