Complex boolean expression to filter on age with date of birth/dead columns

[Marcel Overdijk]

Hi,

I have a customer object/table containing a date of birth and date of death field.

Now I want to create a boolean expression to filter on the age of the customer.

Note that date of death can be null and the the system date needs to be used to determine the age.

Is this possible with querydsl?

Cheers,

Marcel

[corneil....@gmail.com]

Assuming you don't want people that have died in the result.

// Input criteria. If single age then both must be the same.

long minAge;

long maxAge;

LocalDate minDob = LocalDate.now().minusYears(maxAge + 1L).plusDays(1L);

LocalDate maxDob = LocalDate.now().minusYears(minAge);

// Add the following to you query expression assuming your entity has java.sql.Date dateOfBirth.

// For java.sql.Date use: Date minDateOfBirth = Date.valueOf(minDob);

customer.dateOfDate.isNull().and(customer.dateOfBirth.between(Date.valueOf(minDob), Date.valueOf(maxDob)))

Corneil du Plessis about.me/corneil

--
You received this message because you are subscribed to the Google Groups "Querydsl" group.
To unsubscribe from this group and stop receiving emails from it, send an email to querydsl+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Liam Coughlin]

Assuming you do want to include dead people, you'd use coalesce just like you would in sql.

Something like this:

QPerson person = new QPerson("p");

Query<Person> query = queryFactory.select(person)
        .where(SQLExpressions.datediff(DatePart.year,
                Expressions.asDate(person.dateOfBirth),
                Expressions.asDate(new Coalesce<Date>(Date.class, person.dateOfDeath, new DateConstant<Date>(now)).as("ageDate")))
                .lt(55));