how to add Hibernate "createAlias" Child Restriction to parent Entity Restriction

Question

I've got a requirment where I need to apply a search keyword to name and and state of Parent entity (Customer) and also name attribute of child entity Order.

List<Criterion> aliasRestrictionsCriterion = new ArrayList<Criterion>();
Junction quotedLikeRestrictions = Restrictions.disjunction();
quotedLikeRestrictions.add(Restrictions.ilike("customerName", token, MatchMode.ANYWHERE));
quotedLikeRestrictions.add(Restrictions.ilike("customerState", token, MatchMode.ANYWHERE));
restrictions.add(quotedLikeRestrictions);

and to include the child table in the query I'm am doing the following

Criteria alias = criteria.createAlias("order", "order")
.add(Restrictions.ilike("order.name", token, MatchMode.ANYWHERE));

Hibernate is generating the following query but what I need is to have order.name like comparison get added to the parent block.

CUSTOMER this_ inner join ORDER order_ on this_.ORDER_ID=order_.ORDER_ID where 
(lcase(this_.name) like ? or lcase(this_.state) like ?) and (lcase(order_.NAME) 
like ?)

what I want is to have (lcase(order_.NAME) like ?) inside the parent block. So I want is the following

(lcase(this_.name) like ? or lcase(this_.state) like ? or lcase(order_.NAME) like ?)

How can I achieve this ?

Update: this is how I'm calling the criteria

protected List getEntities(List<Criterion> restrictions, String aliasSearchToken) {
    Session session = currentSession();
    Criteria criteria = session.createCriteria(getEntityClass());

    // apply restrictions.
    if (restrictions != null) {
        for (Criterion criterion : restrictions) {
            criteria.add(criterion);
        }
    }

    Criteria alias = criteria.createAlias("order", "order").add(Restrictions.ilike("order.name", searchToken, MatchMode.ANYWHERE));

    List list = criteria.list();
    if (list != null) {
        return list;
    }
    return Collections.emptyList();
}

Update 2:

protected List getEntities(List<Criterion> restrictions, String aliasSearchToken) {
    Session session = currentSession();
    Criteria criteria = session.createCriteria(getEntityClass());

    Junction or = Restrictions.disjunction();
    or.add(Restrictions.ilike("order.name", searchToken, MatchMode.ANYWHERE));
    criteria.createAlias("order", "order").add(or);


    // apply restrictions.
    if (restrictions != null) {
        for (Criterion criterion : restrictions) {
            criteria.add(criterion);
        }
    }

    List list = criteria.list();
    if (list != null) {
        return list;
    }
    return Collections.emptyList();
}

produced select ....... from CUSTOMER this_ inner join ORDER order_ on this_.ORDER_ID=order_.uid where (lcase(order_.NAME) like ?) and (lcase(this_.name) like ? or lcase(this_.state) like ?)

the orderName is just before but still not in the second or statement ... I'm so confused.

JB Nizet JB Nizet · Accepted Answer · 2014-03-04T05:31:00

Well, instea of adding the restriction to the Criteria (which creates an AND), you must add it to the quotedLikeRestrictions disjunction:

quotedLikeRestrictions.add(Restrictions.ilike("order.name", token, MatchMode.ANYWHERE));

how to add Hibernate "createAlias" Child Restriction to parent Entity Restriction

1 Answers