spring-data-jpa-datatables
spring-data-jpa-datatables copied to clipboard
Published
20 hours ago •
darrachequesne
darrachequesne
QueryDSL predicates with @ManyToOne relationships do not work
Currently, @ManyToOne relationships with QDataTablesRepository break global search:
Server:
@Entity
public class User {
@Id private Integer id;
@ManyToOne
@JoinColumn(name = "id_address")
private Address address;
}
@Entity
public class Address {
@Id private Integer id;
}
Client:
$('table#users').DataTable({
columns : [ {
data : 'id'
}, {
data : 'address.id',
// searchable: false,
render: function(data, type, row) {
return data ? data : '';
}
}]
});
The countQuery issued by QueryDslJpaRepository.findAll(Predicate predicate, Pageable pageable) triggers the following query:
select count(user0_.id) as col_0_0_
from user_ user0_
cross join address address1_
where
user0_.id_address=address1_.id
and (lower(cast(user0_.id as char)) like ? escape '\'
or lower(cast(address1_.id as char)) like ? escape '\')
To add a proper LEFT JOIN, an (ugly) workaround would be to manually add JPQLQuery countQuery = createQuery(predicate).leftJoin(entity.get("address")). But if someone has a better idea, I'm open to suggestions!
Update: since QueryDSL generates a leftJoin instead of an innerJoin when a sort is applied on the given column (ref), the following workaround works pretty well:
var table = $('table#users').DataTable({
orderFixed: [ <your-column-id>, 'asc' ],
});
