0

I don´t get that simple query working and i don´t now why. i have three entities (just semantics code):

Profile {
@OneToMany
Set<ProfileClubMapping> clubs
String username
...
}

ProfileClubMapping {
@ManyToOne
Profile profile
@ManyToOne
Club club
...
}

Club {
@OneToMany
Set<ProfileClubMapping> profiles
...
}

now i want to select every profile which contains a string in the username and the ProfileClubMapping with a certain Club id. If no ProfileClubMapping exists just return null for the ProfileClubMapping. I tried something like that:

SELECT p,pcm FROM Profile p LEFT JOIN p.clubs pcm ON pcm.club.id = :clubId WHERE UPPER(p.username) = UPPER(:str)

but this query only returns me the profiles which have an ProfileClubMapping. Anyone an idea?

EDIT: maybe the sql query helps:

SELECT p.USERNAME, pcm.CLUB_ID FROM PROFILE AS p LEFT JOIN PROFILECLUBMAPPING AS pcm ON pcm.PROFILE_ID = p.ID AND pcm.CLUB_ID = :clubId WHERE  p.USERNAME LIKE '%:username%';
perotom
  • 851
  • 13
  • 33

1 Answers1

0

Us LEFT OUTER JOIN instead. The behaviour of join is the same as with normal SQL. See for example -> http://www.w3schools.com/sql/sql_join_left.asp

mh-dev
  • 5,264
  • 4
  • 25
  • 23
  • Probably because your explizitly query for a specific club id. You should add or pcm IS NULL or something like that. – mh-dev May 07 '15 at 19:17
  • how would you accomplish something like that? – perotom May 07 '15 at 19:23
  • Something like this: select p from Profil p left outer join p.clubs c Where UPPER(p.username) = UPPER(:str) and (c.id=:clubId or c.id is null) – mh-dev May 07 '15 at 19:29
  • sadly that doesn#t work. i added the sql query in my post. maybe that helps? – perotom May 07 '15 at 19:31
  • The added query is far away from that what I've posted and please give more detail about what "doesn't work" means in detail. A side node I know that it works. I write atm a blog about hibernate basics so i had code ready to use and i tested it. – mh-dev May 07 '15 at 19:34
  • in your query the condition (c.id=:clubId or c.id is null) doesn't work because c is not the type club but ProfileClubMapping so it should be something like that: (c.club.id =:clubId or c.club.id is null). if i try it like that eclipse link says is not a valid expression. Thank you really much for your help but is it possible to just turn the mysql query to a jpql? – perotom May 07 '15 at 19:39
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/77243/discussion-between-mh-dev-and-user2630406). – mh-dev May 07 '15 at 19:44