CODEWITHSUNDEEP
spring-data-jpaquerydsl
Deividi Cavarzan
Deividi Cavarzan

Reputation: 10110

QueryDsl - OR statement not working

I have the following QueryDSL query:

    QCustomer customer = QCustomer.customer;

    BooleanBuilder builder = new BooleanBuilder();
    builder.or(customer.person.name.containsIgnoreCase(query));
    builder.or(customer.company.name.containsIgnoreCase(query));

    return builder;

And I expect to get results from Persons that contains the name = query and/or Companies that contains the query parameter. But I get nothing.

This is my Customer class mapping:

@OneToOne(orphanRemoval = false, optional = true, cascade = CascadeType.ALL)
private Company company;

@OneToOne(orphanRemoval = false, optional = true, cascade = CascadeType.ALL)
private Person person;

Did someone knows what I'm missing here?

I expect to get a query like this: 

select o 
from Customer 
where o.person.name like '%:name%' or o.company.name like '%:name%'

This is the generated query:

select 
    count(customer0_.uid) as col_0_0_ 
from 
    Customer customer0_ 
cross join 
    Person person1_ 
cross join 
    Company company2_ 
where 
    customer0_.person_uid=person1_.uid 
    and customer0_.company_uid = company2_.uid 
    and (lower(person1_.name) like ? escape '!' or lower(company2_.name) like ? escape '!') limit ?

It uses a count because it's the first query that Spring Data use to paginate the result.

Upvotes: 1

Views: 3797

Answers (1)

Timo Westkämper
Timo Westkämper

Reputation: 22180

The query looks ok. Most probably you get wrong results because the implicit property based joins make the joins inner joins.

Using left joins you might get the results you need.

QPerson person = QPerson.person;
QCompany company = QCompany.company;
BooleanBuilder builder = new BooleanBuilder();
builder.or(person.name.containsIgnoreCase(str));
builder.or(company.name.containsIgnoreCase(str));

query.from(customer)
     .leftJoin(customer.person, person)
     .leftJoin(customer.company, company)
     .where(builder);

Upvotes: 3

Related Questions