Hi there,
I'd like to ask for an advice with a problem regarding OneToMany/ManyToMany mappings. Basically, what I'd like to do is to create named query like this:
<code>"SELECT ch FROM channel ch WHERE ch.channelTypes IN (:typeList)"</code>
where :typeList parameter is collection of desired ChannelType instances (or Integers, if necessary).
channelTypes field in Channel entity is ManyToMany mapping defined as following:
<code>
@ManyToMany(targetEntity=ChannelType.class, cascade = CascadeType.ALL)
@JoinTable(name = "channel_type_rel", joinColumns = { @JoinColumn(name = "channel_id", referencedColumnName = "channel_id") }, inverseJoinColumns = { @JoinColumn(name = "channel_type_id", referencedColumnName = "channel_type_id") })
private List<ChannelType> channelTypes;
</code>
Resulting SQL query then should look like this:
<code>SELECT ch.* FROM channel ch LEFT JOIN channel_type_rel ctr ON (ctr.channel_id = ch.channel_id) LEFT JOIN channel_type ct ON (ctr.channel_type_id = ct.channel_type_id) WHERE ct.channel_type_id IN (:channelTypes) </code>
Apparently, this does not works, so I'd like to ask if there's some way how to achieve this (does not matter how), preferably with pure JPQL, as code is deployed to two environments, one with Hibernate and the other with TopLink. If it's not possible with pure JPQL, HQL is preferred then.
many thanks in advance
Matthes