Group by statement is not rendered with criteria api [message #1015251] |
Wed, 27 February 2013 21:40 |
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 #1015440 is a reply to message #1015251] |
Thu, 28 February 2013 15:28 |
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
|
|
|
Powered by
FUDForum. Page generated in 0.03909 seconds