Reputation: 530
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.
Upvotes: 2
Views: 347
Reputation: 11551
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.
Upvotes: 1