CODEWITHSUNDEEP
ormlite
Basileus
Basileus

Reputation: 140

"Or" statement - ORMLite

I'm tring to do this query with ORMLite but I just can't use the or() statement properly.

SELECT DISTINCT x FROM x x INNER JOIN x.w w WHERE :date >= x.startDate AND w.company.id = :companyId AND w.status = :status AND x.status = :status AND (x.endDate = NULL OR x.endDate >= :date)

My code:

QueryBuilder<x, Integer> xQB = this.xDao.queryBuilder();
xQB.where().eq("status", StatusEnum.ENABLED).and().le("startDate", date)
.and().ge("endDate", date).or().isNull("endDate");

If date is less than startDate this state stil returning values of endDate equal null. If I remove the or() statement everything works fine.

Thanks.

Upvotes: 1

Views: 2036

Answers (1)

Gray
Gray

Reputation: 116848

I have a feeling you are getting confused around the AND and OR grouping. Any and() or or() operation (without args) takes the previous element on the stack and combines it with the next argument and then pushes the result on the stack. So a AND b AND c OR e turns into approximately (((a AND b) AND c) OR e).

The Where class also has and(...) and or(...) method that take arguments which wrap the comparison in parenthesis. This is useful in situations like yours when you need to be explicit about what tow things you are comparing. I'd change your's to be:

QueryBuilder<x, Integer> xQB = this.xDao.queryBuilder();
Where<x, Integer> where = xQB.where();
where.and(where.eq("status", StatusEnum.ENABLED),
    where.and(where.le("startDate", date),
        where.or(where.ge("endDate", date), where.isNull("endDate"))));

This should generate approximately `(a AND (b AND (c OR d)))' which seems to be what you want.

To see the various ways to construct queries, check out the docs:

http://ormlite.com/docs/building-queries

Upvotes: 6

Related Questions