I've created a class with static methods that return Querydsl BooleanExpressions so I can simply pass these expressions to the findAll() or findOne() methods in my Spring Data repositories, as well as reuse them throughout my application. Like here: http://www.petrikainulainen.net/programming/spring-framework/spring-data-jpa-tutorial-part-five-querydsl/.
The issue that I'm having is that the BooleanExpression that's being returned from my methods is generating queries that seem to be sub-optimal (at least for my case).
Method that returns the BooleanExpression:
public static BooleanExpression byExternalIdAndProviderId(
final String externalId, final Long providerId) {
QEListing listing = QEListing.eListing;
return listing.externalIds.any().externalId.equalsIgnoreCase(externalId)
.and(listing.externalIds.any().provider.id.eq(providerId));
}
Query that is generated:
select elisting0_.id as id1_24_,
elisting0_.address1 as address2_24_,
elisting0_.address2 as address3_24_,
elisting0_.business_name as business4_24_,
elisting0_.city_id as city10_24_,
elisting0_.created_date as created5_24_,
elisting0_.latitude as latitude6_24_,
elisting0_.longitude as longitud7_24_,
elisting0_.master_ext_id as master11_24_,
elisting0_.modified_date as modified8_24_,
elisting0_.state_id as state12_24_,
elisting0_.zip as zip13_24_,
elisting0_.zip_4 as zip9_24_,
elisting0_1_.vanity_name as vanity1_34_,
elisting0_2_.account_id as account1_2_
from listings elisting0_
left outer join vanity_names elisting0_1_ on elisting0_.id=elisting0_1_.listing_id
left outer join accounts_to_listings elisting0_2_ on elisting0_.id=elisting0_2_.listing_id
where (
exists (
select 1
from external_ids eexternali1_
where (
eexternali1_.id in (
select externalid2_.external_id
from listing_to_external_id externalid2_
where elisting0_.id=externalid2_.listing_id))
and lower(eexternali1_.external_id)='123456'
)
)
and (
exists (
select 1
from external_ids eexternali3_
where (
eexternali3_.id in (
select externalid4_.external_id
from listing_to_external_id externalid4_
where elisting0_.id=externalid4_.listing_id
)
)
and eexternali3_.provider_id=1
)
)
As opposed to something like this:
select elisting0_.id as id1_24_,
elisting0_.address1 as address2_24_,
elisting0_.address2 as address3_24_,
elisting0_.business_name as business4_24_,
elisting0_.city_id as city10_24_,
elisting0_.created_date as created5_24_,
elisting0_.latitude as latitude6_24_,
elisting0_.longitude as longitud7_24_,
elisting0_.master_ext_id as master11_24_,
elisting0_.modified_date as modified8_24_,
elisting0_.state_id as state12_24_,
elisting0_.zip as zip13_24_,
elisting0_.zip_4 as zip9_24_,
elisting0_1_.vanity_name as vanity1_34_,
elisting0_2_.account_id as account1_2_
from listings elisting0_
left outer join vanity_names elisting0_1_ on elisting0_.id=elisting0_1_.listing_id
left outer join accounts_to_listings elisting0_2_ on elisting0_.id=elisting0_2_.listing_id
join listing_to_external_id a on elisting0_.id = a.listing_id
join external_ids b on a.external_id = b.id
where lower(b.external_id) = '123456'
and b.provider_id = 1
Is there any way to generate a more optimal query, while still offering the re-usability of BooleanExpressions, and also using Spring Data's repositories?
