Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Group by statement is not rendered with criteria api
Group by statement is not rendered with criteria api [message #1015251] Wed, 27 February 2013 21:40 Go to next message
Mathias Hauser is currently offline Mathias Hauser
Messages: 2
Registered: February 2013
Junior Member
The following code should return an orderd list of word entities. The problem here is that i use an aggregate function in the order section but this throws an error because the group by is not rendered.

Generated sql = SELECT t1.ID AS a1, t1.CHANGED AS a2, t1.CREATED AS a3, t1.DELETED AS a4, t1.IGNORED AS a5, t1.TEXT AS a6 FROM FEEDENTRYWORD t0, WORD t1 WHERE ((LOWER(t1.TEXT) LIKE ? AND (t1.IGNORED = ?)) AND (t1.ID = t0.WORD_ID)) ORDER BY SUM(t0.OCCURRENCES) DESC LIMIT ? OFFSET ?

Do I something wrong here or is something wrong with eclipselink?

CriteriaQuery<Word> criteriaQuery = criteriaQuery(clazz);
		Root<Word> root = root(criteriaQuery, clazz);
		Root<FeedEntryWord> rootFeed = root(criteriaQuery, FeedEntryWord.class);
		criteriaQuery.select(root);
		ignoredWhere(criteriaQuery, root, rootFeed, searchFields, searchText);
		criteriaQuery.groupBy(root);
		criteriaQuery.orderBy(criteriaBuilder.desc(criteriaBuilder.sum(rootFeed.<Integer> get("occurrences"))));
		Query query = query(criteriaQuery);
		setFirstMax(query, first, max);
		List<Word> words = query.getResultList();
		printSQL(query);
		return words;
Re: Group by statement is not rendered with criteria api [message #1015423 is a reply to message #1015251] Thu, 28 February 2013 14:33 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1034
Registered: July 2009
Senior Member
I've always assumed using group by on an entity wasn't allowed, but looking at the spec, I was wrong. Please file an EclipseLink bug, and try the equivalent query in JPQL to see if that might workaround the problem.

Best Regards,
Chris
Re: Group by statement is not rendered with criteria api [message #1015440 is a reply to message #1015251] Thu, 28 February 2013 15:28 Go to previous message
Mathias Hauser is currently offline Mathias Hauser
Messages: 2
Registered: February 2013
Junior Member
I also tried every column separately with the same result.
Its possible that the problem is that the aggregate function is only in the order part so the eclipselink does some optimizations and removes it.

I checked it with a Named Query which worked as it should.

@NamedQuery(name = "word.max", query = "Select w From Word w, FeedEntryWord few Where w.ignored = true and few.word = w group by w order by sum(few.occurrences) desc ")

leads to

SELECT t0.ID AS a1, t0.CHANGED AS a2, t0.CREATED AS a3, t0.DELETED AS a4, t0.IGNORED AS a5, t0.TEXT AS a6 FROM WORD t0, FEEDENTRYWORD t1 WHERE ((t0.IGNORED = ?) AND (t1.WORD_ID = t0.ID)) GROUP BY t0.ID, t0.CHANGED, t0.CREATED, t0.DELETED, t0.IGNORED, t0.TEXT ORDER BY SUM(t1.OCCURRENCES) DESC LIMIT ? OFFSET ?

[Updated on: Thu, 28 February 2013 15:55]

Report message to a moderator

Previous Topic:A non-read-only mapping must be defined for the sequence number field
Next Topic:Is here the way to regulate result of query to child tables of an entity.
Goto Forum:
  


Current Time: Sat Oct 25 18:14:12 GMT 2014

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

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