Hello,
I've got the following query (roles and jobs are the same thing):
select o from Role as o
where
(lower(o.name) like :search or lower(o.printCode) like :search)
and o.id not in (
select j.id from JobGroup as jg
join jg.jobs as j
where jg.id = :jgId
)
I'm looking for Roles that match the search criteria but are not already
members of a particular JobGroup.jobs.
The "IN" phrase produces this SQL:
AND 0 = (SELECT COUNT(*) FROM WHERE (t0.roleid IN (
SELECT t3.roleid FROM public.jobgroups t1, public.jobgroupsjobs t2,
public.roles t3
WHERE (t1.jobgroupid <> ?)
AND t1.jobgroupid = t2.jobgroupid
AND t2.jobid = t3.roleid))))
Notice the table name is missing from the count(*) query.
Here is the mapping for JobGroup.jobs:
@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "jobgroupsjobs",
joinColumns = { @JoinColumn(name = "jobgroupid", referencedColumnName =
"jobgroupid") },
inverseJoinColumns = { @JoinColumn(name = "jobid", referencedColumnName =
"roleid") }
)
private List<Role> jobs = new ArrayList<Role>(0);
Is there a problem with my query, mapping or is this a bug?
Thanks.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:[email protected]
