I am facing a problem where I am trying to do a group by and count unique and display only those records where the count is at least some number. I was able to successfuly write some HSQL below that will return me the count and an instance, but I want to further reduce the resultset by saying that I only want to show those where the count > x. Below is the hsql.
Code:
StringBuffer sql = new StringBuffer();
sql.append("select count(lmdv.levelInstance.id) as zcount, lmdv.levelInstance.id as zid from LevelMetaDataValue lmdv where (");
sql.append("(lmdv.typeName = ? and lmdv.value = ?)");
sql.append("OR (lmdv.typeName = ? and lmdv.value = ?)");
sql.append(") group by lmdv.levelInstance.id");
Query query = session.createQuery(sql.toString());
So then I attempted to do it not using HSQL and instead used criteria and detached criteria like so:
Code:
DetachedCriteria detachedCriteria = DetachedCriteria.forClass(LevelMetaDataValue.class);
Criterion metadata1 = Restrictions.and(Restrictions.eq("typeName", "manuals"), Restrictions.eq("value", "0"));
Criterion metadata2 = Restrictions.and(Restrictions.eq("typeName", "imageCount"), Restrictions.eq("value", "0"));
detachedCriteria.add(Restrictions.or(metadata1, metadata2));
detachedCriteria.setProjection(Projections.alias(Projections.count("levelInstance.id"), "zcount"));
detachedCriteria.setProjection(Projections.groupProperty("levelInstance.id"));
However I am still at a loss on how to say only show me those results where the count is > x.
Does anyone know how to do this? Do I need some sort of subselect that checks on count? I have tried to do that unsuccessfully. I am using Oracle as the DB.
Thank you in advance,
Mark
Login
FAQ
Search
