Support for ManyToMany Relation when searching

[hlchanad]

Does this library support ManyToMany relation when searching ?

For example, there are two table, user and role. Each user may has different roles, and each role may be used by different user. Therefore there is one more relation table user_role. In the user datatable, I want to show the roles attached to the user. I can add a List<Role> roles property with annoation @ManyToMany in User class and of cause I can show the roles in the datatable. But the searching function is not working with this field.

[darrachequesne]

Not by default, since we try to prevent firstResult/maxResults specified with collection fetch; applying in memory errors, which happens when one entity has a @OneToMany or a @ManyToMany relationship (and gets spanned over multiple lines in the query results).

You should be able to add a Specification though:

userRepository.findAll(input, (root, query, criteriaBuilder) -> {
    return criteriaBuilder.equal(root.join("role", JoinType.INNER).get("name"), roleName);
});

[nugrohotanjo]

roleName

what is roleName? from where you get the roleName?

[risavkarna]

Not by default, since we try to prevent firstResult/maxResults specified with collection fetch; applying in memory errors, which happens when one entity has a @OneToMany or a @ManyToMany relationship (and gets spanned over multiple lines in the query results).

You should be able to add a Specification though:

userRepository.findAll(input, (root, query, criteriaBuilder) -> {
    return criteriaBuilder.equal(root.join("role", JoinType.INNER).get("name"), roleName);
});

Could you give a complete example here?

[syfercv]

So I had this problem, where I have a datatable of users that have a many-to-many relation with roles and I want for the global search to find user my role.name
I got it to work like this with a List to allow more predicates if you want.

public DataTablesOutput<User> getAllUsers(DataTablesInput input){
		if(!StringUtils.hasText(input.getSearch().getValue())) {
			return userRepository.findAll(input);
		}
		DataTablesOutput<User> udo = userRepository.findAll(input, (root, query, builder) -> {
			List<Predicate> predicates = new ArrayList<>();
			String search = input.getSearch().getValue().toLowerCase();
			Join<User, Role> roles = root.join("roles");
			predicates.add(builder.like(roles.get("name"), "%" + search + "%"));
		        return builder.or(predicates.toArray(new Predicate[predicates.size()]));
		});
		return udo;
	}

The main problem is that you have to download darrachequesne repository classes, include them in your project as a public interface DataTablesRepositoryCustom and change the DataTablesRepositoryImpl so that the part that is defining the AND's becomes OR's

 Specification.where(specificationBuilder.build())
                      .or(additionalSpecification)
                      .or(preFilteringSpecification),
              specificationBuilder.createPageable());