Issue 2 in lambico: Order in pagenated search

[lam...@googlecode.com]

Status: New
Owner: ----
Labels: Type-Defect Priority-Medium

New issue 2 by nico.da...@gmail.com: Order in pagenated search
http://code.google.com/p/lambico/issues/detail?id=2

What steps will reproduce the problem?
1. Create a criteria with an orderd by
2. use the searchPaginatedByCriteria method of Dao
3. dao throws an exception when count elements

What is the expected output? What do you see instead?
a paginated list

What version of the product are you using? On what operating system?
1.0-SNAPSHOT

Please provide any additional information below.

[lam...@googlecode.com]

Updates:
Owner: lucio.be...@gmail.com

Comment #1 on issue 2 by lucio.be...@gmail.com: Order in pagenated search
http://code.google.com/p/lambico/issues/detail?id=2

Hi Nico,
could you post the citeria you are trying to execute and the stacktrace?

[lam...@googlecode.com]

Comment #2 on issue 2 by nico.da...@gmail.com: Order in pagenated search
http://code.google.com/p/lambico/issues/detail?id=2

{{{DetachedCriteria criteria =
DetachedCriteria.forClass(MyPO.class, "myPo");
criteria.createAlias("myPo.user", "u");
criteria.createAlias("myPo.element", "e");

criteria.add(Restrictions.eq("u.id", idUser));

DetachedCriteria subCriteria =
DetachedCriteria.forClass(Element.class, "elem");
subCriteria.add(Restrictions.eqProperty("elem.id", "e.id"));
subCriteria.setProjection(Projections.id());

criteria.add(Subqueries.exists(subCriteria));

criteria.addOrder(Order.desc("myPo.lastusage"));

int pageIndex = 1;
int rowsPerPage = 6;

Page<MyPO> myPo = plantUsageDao.searchPaginatedByCriteria(pageIndex,
rowsPerPage, criteria);}}}

The error says that this_.lastusage should be in a GROUP BY; I've set
show_sql = true and the sql statement is a count with an ORDER BY at the
end.

I've replace the last row with
{{{List<MyPO> myPoList = plantUsageDao.searchByCriteria(criteria);}}}
and it works.

[lam...@googlecode.com]

Comment #3 on issue 2 by lucio.be...@gmail.com: Order in pagenated search
http://code.google.com/p/lambico/issues/detail?id=2

Yes,
sometimes it's not possible to automatically counting the rows of a query.

For these case the Hibernate DAO exposes a searchPaginatedByCriteria method
than don't try to calculate the total row number using count, but that
number is passed as a parameter. In your case the code should be something
like:

{{{

DetachedCriteria criteria = DetachedCriteria.forClass(MyPO.class, "myPo");
criteria.createAlias("myPo.user", "u");
criteria.createAlias("myPo.element", "e");

criteria.add(Restrictions.eq("u.id", idUser));

DetachedCriteria subCriteria =
DetachedCriteria.forClass(Element.class, "elem");
subCriteria.add(Restrictions.eqProperty("elem.id", "e.id"));
subCriteria.setProjection(Projections.id());

criteria.add(Subqueries.exists(subCriteria));

int totalRowCount = plantUsageDao.countByCriteria(criteria);

criteria.addOrder(Order.desc("myPo.lastusage"));

int pageIndex = 1;
int rowsPerPage = 6;

Page<MyPO> myPo = plantUsageDao.searchPaginatedByCriteria(pageIndex,

rowsPerPage, totalRowCount, criteria);
}}}

[lam...@googlecode.com]

Comment #4 on issue 2 by nico.da...@gmail.com: Order in pagenated search
http://code.google.com/p/lambico/issues/detail?id=2

ok, it works!

thanks very much.

only one little thing: count is a long but searchPaginatedByCriteria has an
int param, I think that it will be better to use a long param.

see you soon.
Nico.

[lam...@googlecode.com]

Comment #5 on issue 2 by lucio.be...@gmail.com: Order in pagenated search
http://code.google.com/p/lambico/issues/detail?id=2

yes, you're right. I will change to long in a next release. Thank you for
signalling.