Polymorphic where clause using QueryDsl

Question

I'm trying to use QueryDsl to write a query with a polymorphic where clause.

Since it's a little hard to explain what I want to do in the abstract, I cloned the spring-boot-sample-data-jpa project and modified it to show an example of what I'm trying to do.

I have these model classes, where you'll note that SpaHotel and SportHotel extend the Hotel entity.

I'm trying to write a query that returns all cities containing either a SpaHotel, or a SportHotel whose main sport is of the given type.

I wrote a JPQL version of that query, which is a little ugly (I don't like the sport is null part to signify that it's a Spa Hotel), but seems to return what I want.

But the QueryDsl version of that query doesn't seem to work:

public List<City> findAllCitiesWithSpaOrSportHotelQueryDsl(SportType sportType) {
  QCity city = QCity.city;
  QHotel hotel = QHotel.hotel;

  return queryFactory.from(city)
      .join(city.hotels, hotel)
      .where(
          hotel.instanceOf(SpaHotel.class).or(
              hotel.as(QSportHotel.class).mainSport.type.eq(sportType)
          )
      ).list(city);
}

My test fails with:

test_findAllCitiesWithSpaOrSportHotelQueryDsl(sample.data.jpa.service.CityRepositoryIntegrationTests)  Time elapsed: 0.082 sec  <<< FAILURE!
java.lang.AssertionError:
Expected: iterable over [<Montreal,Canada>, <Aspen,United States>, <'Neuchatel','Switzerland'>] in any order
     but: No item matches: <Montreal,Canada> in [<Aspen,United States>, <'Neuchatel','Switzerland'>]
    at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:20)
    at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:8)
    at sample.data.jpa.service.CityRepositoryIntegrationTests.test_findAllCitiesWithSpaOrSportHotelQueryDsl(CityRepositoryIntegrationTests.java:95)

It seems like my query doesn't return "Montreal", which should be returned, since it contains a SpaHotel.

Also, I wonder if it's normal that QueryDsl would translate my query into a cross join:

select city0_.id as id1_0_, city0_.country as country2_0_, city0_.name as name3_0_
from city city0_
inner join hotel hotels1_
on city0_.id=hotels1_.city_id
cross join sport sport2_
where hotels1_.main_sport_id=sport2_.id and (hotels1_.type=? or sport2_.type=?)

My questions:

1. Why is that query not returning "Montreal", which contains a SpaHotel?
2. Is there a better way of writing that query?
3. Is it normal that the generated SQL does a cross-join? Can't we do a left-join instead like I do in JPQL?

Answer 1

The correct transformation of your JPQL query

String jpql = "select c from City c"
    + " join c.hotels hotel"
    + " left join hotel.mainSport sport"
    + " where (sport is null or sport.type = :sportType)";

is

return queryFactory.from(city)
  .join(city.hotels, hotel)
  .leftJoin(hotel.as(QSportHotel.class).mainSport, sport)
  .where(sport.isNull().or(sport.type.eq(sportType)))
  .list(city);

In your original query this property usage

hotel.as(QSportHotel.class).mainSport

causes the cross join and constraints the query to SportHotels.

Querydsl uses implicit left joins only for paths that are used only in the orderBy part of the query, everything will cause implicit inner joins.