Hi,
Which version of OpenJPA are you using? Using the trunk level code, I
got the generated SQL as:
4306 testSubquery TRACE [main] openjpa.Query - Executing query: [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 )] with parameters: {jgId=1, search=search}
4396 testSubquery TRACE [main] openjpa.jdbc.SQL - <t 21868771, conn
26596606> executing prepstmnt 9449509 SELECT t3.id, t3.name, t3.printCode,
t3.ts FROM Role t3 WHERE ((LOWER(t3.name) LIKE ? ESCAPE '\' OR
LOWER(t3.printCode) LIKE ? ESCAPE '\') AND NOT (t3.id IN (SELECT t2.id FROM
JobGroup t0 INNER JOIN jobgroupsjobs t1 ON t0.jobgroupid = t1.jobgroupid INNER
JOIN Role t2 ON t1.jobid = t2.roleid WHERE (t0.id = ?) ))) [params=(String)
search, (String) search, (int) 1]
----- Original Message ----
From: Daryl Stultz <[email protected]>
To: OpenJPA User List <[email protected]>
Sent: Tue, July 6, 2010 8:45:54 AM
Subject: Missing table name in query
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]
