Create Predicate that searches for element in collection

[Daniel Fazeres]

Hello. I'm fairly new to QueryDSL and I don't know how to do this.

I need to create a Predicate that is true if a collection of children Documents in my Document (I'm using mongo) has id="foo" and role="bar".

I've thought about doing the following (which I'm guessing won't work):

QDepartmentDocument departmentDocument = QDepartmentDocument.departmentDocument;

BooleanBuilder builder = new BooleanBuilder();
builder.and(departmentDocument.employees.any().id.eq("foo"));
builder.and(departmentDocument.employees.any().role.eq("bar"));
builder.build();

I'm building this Predicate in order to send it to a Spring repository implementing QuerydslPredicateExecutor.

The problem with my approach is that it will be true if there is an employee with id=foo and a different employee with role=bar, I'm guessing. I need it to be true only when there is an employee that matches both.

I tried exploring the documentation to no avail.

[Zbynek Vavros]

Hi Daniel,

its funny, we were dealing with exactly same last week :)

The solution we used is to use subquery like this

QDepartmentDocument departmentDocument = QDepartmentDocument.departmentDocument;
QEmployee qEmployee = QEmployee.empolyee;
BooleanExpression expression = JPAExpressions.selectOne()
        .from(qEmployee)
        .where(qEmployee.departmentId.eq(departmentDocument.id)
                .and(qEmployee.id.eq("foo"))
                .and(qEmployee.role.eq("bar")).exists();

and this roughly translates to this SQL




select *
from departmentDocument departmentDocument0_
where exists(select 1
             from employee employee1_
             where employee1_.department_id = departmentDocument.id
               and employee1_.id = 'foo'
               and employee1_.role = 'bar')




Hope it helps, Zbynek

--
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+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/querydsl/0bffbc65-beaf-49be-8f4c-da17c003579co%40googlegroups.com.

[Krishna Parachikapu]

I guess the following way should work using BooleanExpression and MongoRepository. Something like below. 

BooleanExpression idExpression = departmentDocument.employees.any().id.eq("foo");

BooleanExpression roleExpression = departmentDocument.employees.any().role.eq("bar");

List<Employee> employees = employeeRepository.findAll(idExpression.and(roleExpression));

Thanks.

[Daniel Fazeres]

I tried both suggestions. JPAExpressions doesn't work because it throws an exception saying it can't find the javax.persistence.Entity annotation (I'm using mongodb). The two predicates solution doesn't filter departments that have two employees that satisty one of the predicates.

I don't know how aliases work in querydsl. Could that be useful to me?

[Rui Barbosa]

Hi Daniel,

I'm facing the same issue.

I guess the following snippet would work using a Predicate with a SubQueryExpression and QueryDslPredicateExecutor:




QDepartmentDocument departmentDocument = QDepartmentDocument.departmentDocument;
Predicate predicate = new BooleanBuilder();
predicate.and(departmentDocument.employees.any().eqAny(SubQueryExpression);

List<DepartmentDocument> result = departmentRepository.findAll(predicate);

However, I don't find examples of how to build the mentioned SubQueryExpression for Mongo.

If anyone has another suggestion please share :)

Thanks,

Rui