4

I am using spring-boot, spring-data-JPA and Hibernate and I've a Form Entity and a Group Entity and they have a @ManyToMany relationship between them.

@ManyToMany(fetch=FetchType.LAZY)
    @JoinTable(name = "form_group",
        joinColumns = @JoinColumn( name = "form_id", referencedColumnName = "id"), 
        inverseJoinColumns = @JoinColumn(name = "group_id", referencedColumnName = "id"))
    @JsonIgnore
    private Collection<Group> groups;

In FormRepository class I have a method called public List<Form> findByGroups(Collection<Group> userGroups); which takes a parameter of type Collection<Group> and is expected to return all the forms which belong to at least one of the groups passed as the method parameter. Here is the Query: 

@Query("SELECT new com.nsia.model.Form(f.id, f.name, f.description, f.createdAt, f.groups, COUNT(i.id)) from Form f LEFT JOIN f.instances i WHERE f.groups IN (?1) group by f.id")

As you can see the userGroups is of type Collection<Group> and inside the Form Entity it is of the same type. When the method findByGroupis called, it throws java.lang.IllegalArgumentException and here is the full message:

java.lang.IllegalArgumentException: Parameter value [Group {id=4, name='DATA_ENTRY_GROUP', description='DATA ENTRY GROUP'}] did not match expected type [java.util.Collection (n/a)]

I'm sure that userGroups is of type Collection<Group> because here is how i'm getting it inside the FormServiceImpl class:

        Collection<Group> groups = userService.getLoggedInUser().getGroups();
        formsList = formRepository.findByGroups(groups);

There are lots of similar questions in StackOverflow and I've tried every one of them and none worked for me, any help will be highly appreciated. THANKS

jalil
  • 476
  • 1
  • 7
  • 16

1 Answers1

1

What you are trying is not possible.

The constructor expression cannot take a collection as an argument because the result of the under laying SQL select statement is always a table.

So the only thing you can do is to get a cartesian product back like this where you join f.groups.

@Query("SELECT new com.nsia.model.Form(f.id, f.name, f.description, f.createdAt, g, COUNT(i.id)) from Form f LEFT JOIN f.instances JOIN f.groups g WHERE f.groups IN (?1) group by f.id")

So you will get a record per group but that's probably not what you want.

Simon Martinelli
  • 34,053
  • 5
  • 48
  • 82
  • Dear @Simon! THANK YOU for your answer, however, it does not work for me, I tried it and it throws NoViableAltException and says: unexpected token: i. – jalil Jun 11 '19 at 11:54
  • I'm sorry there was a typo in my answer. Please try again. – Simon Martinelli Jun 11 '19 at 11:55
  • Dear @Simon! thank you for bearing with me. Unfortunately, your edited answer also does not work for me. I've literally tried every variation of it and none of them worked. I also wrote the results of each variation of the code in the previous comment, some of which was compile time errors and some with the original problem and for some reason i lost it and couldn't deliver to you. :) – jalil Jun 11 '19 at 12:33
  • 1
    @SimonMartinelli Thank you for this answer, it worked for me with: `@Query("SELECT df FROM DeskFloor df LEFT JOIN df.spaces s WHERE s.id = ?1")` – Renan Ribeiro Mar 02 '21 at 18:07