CODEWITHSUNDEEP
javahibernatehibernate-criteria
Narain Mittal
Narain Mittal

Reputation: 1160

Hibernate criteria to have multiple restrictions on the child collection

I have to write a criteria query with where clause to match both first & last names in the child collection. Both names are in different rows

Tried this, but does not return anything even when the matching data is present, probably because it's trying to match both restrictions on same row.

Criteria criteria = getCurrentSession().createCriteria(Form.class);
criteria.createAlias("responses", "r");
criteria.add(Restrictions
                .conjunction()
                .add(Restrictions.eq("r.id", "firstName"))
                .add(Restrictions.eq("r.value", getFirstName())));
criteria.add(Restrictions
                .conjunction()
                .add(Restrictions.eq("r.id", "lastName"))
                .add(Restrictions.eq("r.value", getLastName())));

Tried this and this gives an exception org.hibernate.QueryException: duplicate association path: responses

Criteria criteria = getCurrentSession().createCriteria(Form.class);
criteria.createAlias("responses", "r1");
criteria.createAlias("responses", "r2");

criteria.add(Restrictions
                .conjunction()
                .add(Restrictions.eq("r1.id", "firstName"))
                .add(Restrictions.eq("r1.value", getFirstName())));
criteria.add(Restrictions
                .conjunction()
                .add(Restrictions.eq("r2.id", "lastName"))
                .add(Restrictions.eq("r2.value", getLastName())));

Any help?

EDIT

It looks like from the description the question wasn't clear. Here is the basic requirement:

Query all records from form class which have (A child response record with id=firstName AND value=someName1) AND (A child response record with id=lastName AND value=someName2)

I am also adding the solution which worked for me, using the subqueries. Not sure if this was the best way, but it solved my problem

Upvotes: 1

Views: 4849

Answers (2)

Narain Mittal
Narain Mittal

Reputation: 1160

I was able to solve the problem using subqueries instead. Looks like hibernate doesn't support multiple joins on the same child record.

Criteria criteria = getCurrentSession().createCriteria(Form.class);             
DetachedCriteria subQuery1 = DetachedCriteria.forClass(Response.class);
subQuery1.add(Restrictions.and(
    Restrictions.eq("id", "firstName").add(
    Restrictions.eq("value", getFirstName())));
subQuery1.setProjection(Projections.property("formId"));

DetachedCriteria subQuery2 = DetachedCriteria.forClass(Response.class);
subQuery2.add(Restrictions.and(
    Restrictions.eq("id", "lastName").add(
    Restrictions.eq("value", getLastName())));
subQuery2.setProjection(Projections.property("formId"));
criteria.add(Restrictions.and(Subqueries.propertyIn("id", subQuery1),
                Subqueries.propertyIn("id", subQuery2)));

Upvotes: 1

jpprade
jpprade

Reputation: 3664

To query with a OR :

Criteria criteria = getCurrentSession().createCriteria(Form.class);
criteria.createAlias("responses", "r");
Junction conditionGroup = Restrictions.disjunction();
conditionGroup.add(Restrictions
                .conjunction()
                .add(Restrictions.eq("r.id", "firstName"))
                .add(Restrictions.eq("r.value", getFirstName())));
conditionGroup.add(Restrictions
                .conjunction()
                .add(Restrictions.eq("r.id", "lastName"))
                .add(Restrictions.eq("r.value", getLastName())));
criteria.add(conditionGroup);

Upvotes: 0

Related Questions