• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

subquery with where clause

 
Ketan Nale
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

I want to write hibernate DetachedCriteria for following SQL query :

my sql query is :

select * from pkg_order
where
pkg_order.sataus='Active'
and
pkg_order.pkg.pkg_usage
>
(select count(id) from user_pkg
where
user_pkg.orderId=pkg_order.id)


i have tried like following.
Can anyone help me to optimise this query??
its giving me correct output but .... still i want the better solution .......

DetachedCriteria subquery = DetachedCriteria.forClass(EduoaksUserPkg.class, "pkgOrderAlias");
subquery.setProjection(Projections.count("pkgOrderAlias."+EduoaksUserPkg.PROP_ID));
subquery.add(Expression.eqProperty("pkgOrderAlias."+EduoaksUserPkg.PROP_PKG_ORDER+"."+EduoaksPkgOrder.PROP_ID, "u.id"));

DetachedCriteria criteria = DetachedCriteria.forClass(EduoaksPkgOrder.class, "u");

criteria.createAlias("u."+EduoaksPkgOrder.PROP_PKG, "pkgAlias");
criteria.createAlias("pkgAlias"+"."+EduoaksPkg.PROP_USAGE, "usageAlias");

criteria.add(Restrictions.eq("u."+EduoaksPkgOrder.PROP_USER, new SakaiUser(currentUserId)));
criteria.add(Restrictions.ge("u."+EduoaksPkgOrder.PROP_EXPIRAY_DATE, new Date()));
criteria.add(Restrictions.eq("u."+EduoaksPkgOrder.PROP_STATUS, "Active"));

Criterion unlimited = Restrictions.eq("usageAlias"+"."+EduoaksPkgUsage.PROP_USAGE, SamSLPLConstants.UNLIMITED_VAL);
Criterion limited = Subqueries.propertyGt("usageAlias"+"."+EduoaksPkgUsage.PROP_USAGE, subquery);
Criterion criterion = Restrictions.or(unlimited, limited);
criteria.add(criterion);

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic