CODEWITHSUNDEEP
javahibernatecriteria
seesee
seesee

Reputation: 1155

Criteria, Subqueries, Group By and Having more

I have a product table with a relationship to color table

a product can have many colors... exp: Product A: has red, green blue yellow.

I wish to find the product which contain at least RED and GREEN.

     DetachedCriteria colorCrit = DetachedCriteria.forClass(Color.class);

        ProjectionList colorList = new Projections.projectionList();
        colorList.add(Projections.groupProperty("productID"));
        colorList.add(Projections.rowCount(),"abc");
        colorCrit.setProjection(colorList);

        colorCrit.add(Restrictions.eq("color", "GREEN")
    colorCrit.add(Restrictions.eq("color", "RED")
    colorCrit.add(Restrictions.eq("abc",2);

    Criteria productCrit = new Criteria(Product.class);

    productCrit.add(Suqueries.in("id",colorCrit));
list<Product> productList = productCrit.list();

I use the above code, but I am unable to achieve the group by having on Projections.rowCount().

I have tried .as but it will result in an additional column which make the detached criteria not suitable Suqueries. (Too many value oracle exception)

colorCrit.add(Restrictions.eq(Projections.rowCount(),2); > does not work because rowcount is not a property = x

select * from product pd where pd.id = (select cr.productID from color cr where cr.color="RED" or cr.color="GREEN" group by  cr.productID having rowcount=2

The above should be the proper SQL query.

May I know is there a solution?

Upvotes: 2

Views: 2537

Answers (1)

JB Nizet
JB Nizet

Reputation: 692231

I would use the following query:

select p from Product p where 
2 = (select count(color.id) from Product p2 
     inner join p2.colors color
     where p2.id = p.id
     and color.color in ('GREEN', 'RED'))

The above can be translated in Criteria by

Criteria c = session.createCriteria(Product.class, "p")

DetachedCriteria sub = DetachedCriteria.forClass(Product.class, "p2");
sub.createAlias("p2.colors", "color");
sub.add(Restrictions.eqProperty("p2.id", "p.id"))
sub.add(Restrictions.in("color.color", new String[] {"RED", "GREEN"}));
sub.setProjection(Projections.count("color.id"));

c.add(Subqueries.eq(2, sub)); // or 2L

The above assumes that you can't have a product which has 2 colors being RED, i.e. that the tuple (color, product_id) has a unique constraint in the table color.

Upvotes: 2

Related Questions