0

I need one row per skill regardless of whether a skill is associated with the user. There are < 1000 rows in skill. However, the SQL generated by JPA/Hibernate is returning skill rows for all users.

JPA

select case when su = :user then 1 else 0 end from Skill s left join s.skillUsers su on su = :user order by s.id

SQL I wrote that works

select case when us.user_id = 1 then 1 else 0 end from skill as s left join user_skills as us on us.skill_id = s.id and us.user_id = 1

SQL produced by Hibernate/JPA that does not work

select case when user2_.id=? then 1 else 0 end as col_0_0_ from skill skill0_ left outer join user_skills skillusers1_ on skill0_.id=skillusers1_.skill_id left outer join user user2_ on skillusers1_.user_id=user2_.id and (user2_.id=?) order by skill0_.id

Skill entity

@BatchSize(size = 100)
@ManyToMany(mappedBy = "userSkills")
@LazyCollection(LazyCollectionOption.EXTRA)
public Set<User> getSkillUsers() {
    return skillUsers;
}

User entity

@JoinTable(name = "user_skills", joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"), uniqueConstraints = @UniqueConstraint(name = "UK_user_skill", columnNames = {"user_id", "skill_id"}), inverseJoinColumns = @JoinColumn(name = "skill_id", referencedColumnName = "id"))
@ManyToMany
@LazyCollection(LazyCollectionOption.EXTRA)
public Set<Skill> getUserSkills() {
    return userSkills;
}
rmarcd
  • 1
  • 1
  • 1
  • The SQL's problem is that you need the user2_.id=? to be on the 1st left join involving user_skills. The reason you get so many records is because it's showing all the skills, all the user skills and then only those users whose ID matches the parameter entered. I don't know enough about JPA to say how to do this; thus a comment. But you're problem is there. – xQbert May 07 '18 at 13:25
  • Maybe you need to search the association instead of the entities: https://stackoverflow.com/questions/12563445/jpa-how-to-filter-an-association or maybe https://stackoverflow.com/questions/17154676/criteria-jpa-2-with-3-tables? – xQbert May 07 '18 at 13:40

0 Answers0