JPA criteriabuilder where statement

Question

I would like to achieve the following where statement in my CriteriaQuery.

  (Event.time >= NOW() 
     OR (Event.time >= '$clubHours' && (`EventSelectType`.`type`='cafe' || `EventSelectType`.`type`='DJ')) 
     OR (Event.endtime >= NOW() && (`EventSelectType`.`type`='festival' OR `EventSelectType`.`type`='dj' OR `EventSelectType`.`type`='cafe')))

I tried the following ;

eQr.where(
        cB.or(
            cB.or(
                    cB.greaterThanOrEqualTo(eventRoot.<Date>get("time"), curDate)   
                ),
            cB.or(
                    cB.and(
                    cB.greaterThanOrEqualTo(eventRoot.<Date>get("time"), cal.getTime())
                    ),
                        cB.or(
                                cB.equal(eventType.<String>get("type"), "cafe"),
                                cB.equal(eventType.<String>get("type"), "DJ")
                                )
                ),
            cB.or(
                    cB.and(
                    cB.greaterThanOrEqualTo(eventRoot.<Date>get("endtime"), curDate)
                    ),
                        cB.or(
                                cB.equal(eventType.<String>get("type"), "festival"),
                                cB.equal(eventType.<String>get("type"), "DJ"),
                                cB.equal(eventType.<String>get("type"), "cafe")
                                )
                )
        ),
        cB.equal(eventRoot.<Integer>get("id"), eventID),
        cB.equal(eventRoot.<Integer>get("active"), 1)
        );

But all this generates is one big OR statement

(event0_.time>=? or event0_.time>=? or eventselec13_.type=? or eventselec13_.type=? or event0_.endtime>=? or eventselec13_.type=? or eventselec13_.type=? or eventselec13_.type=?)

How could I get the result I'm looking for?

EDIT

I currently have this

Predicate eventTypeIscafeORDJ = cB.or(
        cB.equal(eventType.<String>get("type"), "cafe"),
        cB.equal(eventType.<String>get("type"), "DJ")
    );
Predicate eventTypeIsFestivalDJORCafe = cB.or(
        cB.equal(eventType.<String>get("type"), "cafe"),
        cB.equal(eventType.<String>get("type"), "DJ"),
        cB.equal(eventType.<String>get("type"), "festival")
        );

    Predicate timeGreaterOrEqualTo = cB.greaterThanOrEqualTo(eventRoot.<Date>get("time"), cal.getTime());
    Predicate endTimeGreaterOrEqualTo = cB.greaterThanOrEqualTo(eventRoot.<Date>get("endtime"), curDate);
    Predicate equalToId = cB.equal(eventRoot.<Integer>get("id"), eventID);

    eQr.where(timeGreaterOrEqualTo, cB.or( cB.and( timeGreaterOrEqualTo, eventTypeIscafeORDJ ), cB.and(endTimeGreaterOrEqualTo, eventTypeIsFestivalDJORCafe )), equalToId);

Which outputs

where event0_.time>=? and (event0_.time>=? and (eventselec13_.type=? or eventselec13_.type=?) or event0_.endtime>=? and (eventselec13_.type=? or eventselec13_.type=? or eventselec13_.type=?)) and event0_.id=633188

But it has to be

where (event0_.time>=? OR (event0_.time>=? and (eventselec13_.type=? or eventselec13_.type=?)) or (event0_.endtime>=? and (eventselec13_.type=? or eventselec13_.type=? or eventselec13_.type=?))) and event0_.id=633188

It still isn't exactly what I want and I cant seem to get it work the way I want it to.

Answer 1

Start with your obvious, first problem:

Event.time >= '$clubHours' && (`EventSelectType`.`type`='cafe' || `EventSelectType`.`type`='DJ')) 

This should be constructed as

cB.and(
    cB.greaterThanOrEqualTo(eventRoot.<Date>get("time"), cal.getTime()), 
    cB.or(
        cB.equal(eventType.<String>get("type"), "cafe"),
        cB.equal(eventType.<String>get("type"), "DJ")
    )
), ...

Instead you have:

cB.and(
    cB.greaterThanOrEqualTo(eventRoot.<Date>get("time"), cal.getTime())
),
cB.or(
    cB.equal(eventType.<String>get("type"), "cafe"),
    cB.equal(eventType.<String>get("type"), "DJ")
)

So, basically, you need to be sure to look at your parentheses and not your indenting in order to understand what your code is doing. As a side note, you would be better off constructing this predicate in steps so your code is easier to read and understand. E.g.:

Predicate eventTypeIscafeORDJ = cB.or(
    cB.equal(eventType.<String>get("type"), "cafe"),
    cB.equal(eventType.<String>get("type"), "DJ")
);
Predicate timeGreaterOrEqualTo = cB.greaterThanOrEqualTo(eventRoot.<Date>get("time"), cal.getTime());
eQr.where( cB.and( timeGreaterOrEqualTo, eventTypeIscafeORDJ ) );

The database is almost always the slower part of the code and the optimizer will probably make the same runtime out or either set of code, so you should make it easier on yourself and others. Further, this is the time tested way of debugging a coding problem, especially when you see all those parens.