Spring datatables problem when i use innerJoin

metalller

this is sql generated by jpa spring datattables plugin
select
transactio0_.transactionid as transact1_26_,
transactio0_.created_by as created_2_26_,
transactio0_.creation_date as creation3_26_,
transactio0_.last_modified_by as last_mod4_26_,
transactio0_.last_modified_date as last_mod5_26_,
transactio0_.accountid as account16_26_,
transactio0_.collection_amount as collecti6_26_,
transactio0_.account_from_number as account_7_26_,
transactio0_.account_to_number as account_8_26_,
transactio0_.exchange_from as exchang17_26_,
transactio0_.exchange_to as exchang18_26_,
transactio0_.payment_date as payment_9_26_,
transactio0_.processing_date as process10_26_,
transactio0_.order_status as order_s11_26_,
transactio0_.payment_amount as payment12_26_,
transactio0_.payment_method as payment13_26_,
transactio0_.payment_status as payment14_26_,
transactio0_.transaction_type as transac15_26_
from
transaction transactio0_ cross
join
operation_type_locale operationt1_ cross
join
operation_type_locale operationt2_
where
(
lower(cast(transactio0_.transactionid as char)) like ? escape ?
or lower(cast(transactio0_.creation_date as char)) like ? escape ?
or lower(cast(transactio0_.payment_amount as char)) like ? escape ?
or lower(cast(transactio0_.collection_amount as char)) like ? escape ?
or lower(cast(transactio0_.payment_method as char)) like ? escape ?
or lower(cast(transactio0_.transaction_type as char)) like ? escape ?
or lower(cast(transactio0_.payment_status as char)) like ? escape ?
or lower(cast(transactio0_.order_status as char)) like ? escape ?
)
and transactio0_.accountid=?
and transactio0_.payment_status=operationt2_.latin
and operationt2_.locale=?
** and (
lower(operationt1_.localized) like ? escape ?
) **
order by
transactio0_.transactionid asc limit ?

when i use global serach with additional specification with spring datatables

Page<T> data = findAll(
Specification.where(specificationBuilder.build())
.and(additionalSpecification)
.and(preFilteringSpecification),
specificationBuilder.createPageable());

add additional specification with AND Clause, causes problem ,when i want to use LIKE on join tables.
it's added by AND cluase not OR clause
here
** and (
lower(operationt1_.localized) like ? escape ?
) **
my question is how can i add additional specification For LIKE cluase with joined table?

for example
Page<T> data = findAll(
Specification.where(specificationBuilder.build())
.and(additionalSpecification)
.and(preFilteringSpecification),
.or(customGlobalFilteringSpecification),
specificationBuilder.createPageable());

please help me????

allan

this is sql generated by jpa spring datattables plugin

You'd need to talk to the authors of that library. We don't write and publish the Spring plug-in for DataTables I'm afraid. It looks like there might be an error in how the SQL is being built, but that really would be one for them.

Allan