I have 3 entities: User, Shelter and ShelterUsers
ShelterUsers is a join table for many to many relationship between Shelter and User with additional column. (design based on this suggestion)
I also have a @OneToMany relationship from User to Role which also uses a join table but without an additional field so there is no entity for that table
When I get the User from the database Roles are also attached with a join if I set the fetch to EAGER and they're loaded with additional query if I set fetch to LAZY (even without calling the user.getRoles() explicitly, which I also don't understand).
Problem is that ShelterUsers are not attached to user, no matter if I set it to EAGER or LAZY. After inspecting the sql that hibernate generates it seems like it generates the wrong sql:
First query:
SELECT user0_.id AS id1_7_0_,
       user0_.created_at AS created_2_7_0_,
       user0_.email AS email3_7_0_,
       user0_.first_name AS first_na4_7_0_,
       user0_.last_name AS last_nam5_7_0_,
       user0_.password AS password6_7_0_,
       user0_.updated_at AS updated_7_7_0_,
       user0_.username AS username8_7_0_,
       roles1_.user_id AS user_id1_8_1_,
       role2_.id AS role_id2_8_1_,
       role2_.id AS id1_4_2_,
       role2_.name AS name2_4_2_
FROM users user0_
LEFT OUTER JOIN users_roles roles1_ ON user0_.id=roles1_.user_id
LEFT OUTER JOIN ROLES role2_ ON roles1_.role_id=role2_.id
WHERE user0_.id=?
Second query:
SELECT shelters0_.shelter_id AS shelter_3_6_0_,
       shelters0_.id AS id1_6_0_,
       shelters0_.id AS id1_6_1_,
       shelters0_.shelter_id AS shelter_3_6_1_,
       shelters0_.user_id AS user_id4_6_1_,
       shelters0_.user_role AS user_rol2_6_1_,
       user1_.id AS id1_7_2_,
       user1_.created_at AS created_2_7_2_,
       user1_.email AS email3_7_2_,
       user1_.first_name AS first_na4_7_2_,
       user1_.last_name AS last_nam5_7_2_,
       user1_.password AS password6_7_2_,
       user1_.updated_at AS updated_7_7_2_,
       user1_.username AS username8_7_2_
FROM shelter_users shelters0_
LEFT OUTER JOIN users user1_ ON shelters0_.user_id=user1_.id
WHERE shelters0_.shelter_id=?
Where clause should be WHERE user1_.id = ?
Here is all relevant code:
User:
@Entity
@Data
@Table(name = "users")
public class User {
  ...
  @OneToMany(fetch = FetchType.EAGER)
  @JoinTable(name = "users_roles",
    joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"),
    inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id")
  )
  private Collection<Role> roles = new ArrayList<>();
  @OneToMany(mappedBy = "shelter")
  private Collection<ShelterUsers> shelters = new ArrayList<>();
  ...
}
Shelter:
@Entity
...
@Table(name = "shelters")
public class Shelter {
  ...
  @OneToMany(mappedBy = "user")
  private List<ShelterUsers> users = new ArrayList<>();
}
ShelterUsers:
@Entity
public class ShelterUsers {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;
  @ManyToOne
  @JoinColumn(name = "user_id", referencedColumnName = "id")
  private User user;
  @ManyToOne
  @JoinColumn(name = "shelter_id", referencedColumnName = "id")
  private Shelter shelter;
  @Enumerated(EnumType.STRING)
  @Column(name = "user_role")
  private ShelterUserRole userRole;
}
Recap:
Roles get loaded with user no matter the fetch type.
ShelterUsers are not loaded
JSON example:
{
    ...
    "roles": [
        {
            "id": 1,
            "name": "ROLE_ADMIN"
        }
    ],
    "shelters": [],
    ...
}
What I would ideally want to achieve is this:
{
    "roles": [
        {
            "id": 1,
            "name": "ROLE_ADMIN"
        }
    ],
    "shelters": [
      {
        //Shelter entity (not ShelterUser) 
      },  
    ]
}
