You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Daryl Stultz <da...@6degrees.com> on 2010/07/06 17:45:54 UTC
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:daryl.stultz@opentempo.com
Re: Missing table name in query
Posted by Daryl Stultz <da...@opentempo.com>.
On Tue, Jul 6, 2010 at 2:50 PM, Fay Wang <fy...@yahoo.com> wrote:
> Hi,
> Which version of OpenJPA are you using?
Sorry, I'm usually pretty good about including that.
I'm on 1.2.1.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com
Re: Missing table name in query
Posted by Michael Dick <mi...@gmail.com>.
I just fixed the release notes (well for 1185 anyway), sorry for not
checking before I sent the previous email.
I'm not sure if there's a query workaround off the top of my head. Will try
to take a closer look tonight / tomorrow though.
-mike
On Wed, Jul 7, 2010 at 1:49 PM, Daryl Stultz <da...@6degrees.com> wrote:
> On Wed, Jul 7, 2010 at 2:16 PM, Michael Dick <michael.d.dick@gmail.com
> >wrote:
>
> > Hi Daryl,
> >
> > There's no real timeline for a 1.3 release, if there's demand for one
> that
> > can change :-).
> >
>
> OK, then, +1 for me! I think there are some other bugs fixed that I have
> run
> into, too.
>
> Fay says 1185 is fixed for 1.3 but I don't see it in the list. The JIRA
> just
> states 2.0:
> https://issues.apache.org/jira/browse/OPENJPA-1185
>
> Is there any query-based workaround for 1185? I am currently passing in a
> list which means I have to preload the list when I don't really need it:
>
> select o from Role as o
> where
> (lower(o.name) like :search or lower(o.printCode) like :search)
> and o.id not in (:jgJobsIds)
>
> query.setParameter("jgJobsIds", jobGroup.getJobs()); // not quite like
> this... extracted id's
>
> Thanks.
>
> --
> Daryl Stultz
> _____________________________________
> 6 Degrees Software and Consulting, Inc.
> http://www.6degrees.com
> http://www.opentempo.com
> mailto:daryl.stultz@opentempo.com
>
Re: Missing table name in query
Posted by Daryl Stultz <da...@6degrees.com>.
On Wed, Jul 7, 2010 at 2:16 PM, Michael Dick <mi...@gmail.com>wrote:
> Hi Daryl,
>
> There's no real timeline for a 1.3 release, if there's demand for one that
> can change :-).
>
OK, then, +1 for me! I think there are some other bugs fixed that I have run
into, too.
Fay says 1185 is fixed for 1.3 but I don't see it in the list. The JIRA just
states 2.0:
https://issues.apache.org/jira/browse/OPENJPA-1185
Is there any query-based workaround for 1185? I am currently passing in a
list which means I have to preload the list when I don't really need it:
select o from Role as o
where
(lower(o.name) like :search or lower(o.printCode) like :search)
and o.id not in (:jgJobsIds)
query.setParameter("jgJobsIds", jobGroup.getJobs()); // not quite like
this... extracted id's
Thanks.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com
Re: Missing table name in query
Posted by Michael Dick <mi...@gmail.com>.
Hi Daryl,
There's no real timeline for a 1.3 release, if there's demand for one that
can change :-).
There are 16 open JIRA
issues<https://issues.apache.org/jira/secure/IssueNavigator.jspa?reset=true&mode=hide&sorter/order=DESC&sorter/field=priority&resolution=-1&pid=12310351&fixfor=12313326>associated
with 1.3 - we can probably close / defer some of them to the next
release though.
The release notes for 1.3.0 (if it was released today) can be found
here<https://issues.apache.org/jira/secure/ReleaseNote.jspa?projectId=12310351&styleName=Html&version=12313326>
(might
need some polishing but this should give you the gist).
hth
-mike
On Tue, Jul 6, 2010 at 3:01 PM, Daryl Stultz <da...@6degrees.com> wrote:
> On Tue, Jul 6, 2010 at 3:45 PM, Fay Wang <fy...@yahoo.com> wrote:
>
> > Hi Daryl,
> > The subquery problem is fixed in JIRA-1185. Please try trunk level
> code
> > or openjpa1.3.
> >
> > Thanks, Fay. 1.3 is not released, though. Any idea when it might be?
> Also,
> what is in the change log / release notes? Thanks.
>
> --
> Daryl Stultz
> _____________________________________
> 6 Degrees Software and Consulting, Inc.
> http://www.6degrees.com
> http://www.opentempo.com
> mailto:daryl.stultz@opentempo.com
>
Re: Missing table name in query
Posted by Daryl Stultz <da...@6degrees.com>.
On Tue, Jul 6, 2010 at 3:45 PM, Fay Wang <fy...@yahoo.com> wrote:
> Hi Daryl,
> The subquery problem is fixed in JIRA-1185. Please try trunk level code
> or openjpa1.3.
>
> Thanks, Fay. 1.3 is not released, though. Any idea when it might be? Also,
what is in the change log / release notes? Thanks.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com
Re: Missing table name in query
Posted by Fay Wang <fy...@yahoo.com>.
Hi Daryl,
The subquery problem is fixed in JIRA-1185. Please try trunk level code or openjpa1.3.
Regards,
Fay
----- Original Message ----
From: Daryl Stultz <da...@6degrees.com>
To: users@openjpa.apache.org
Sent: Tue, July 6, 2010 12:07:25 PM
Subject: Re: Missing table name in query
On Tue, Jul 6, 2010 at 2:50 PM, Fay Wang <fy...@yahoo.com> wrote:
> Hi,
> Which version of OpenJPA are you using?
Sorry, I'm usually pretty good about including that.
I'm on 1.2.1.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com
Re: Missing table name in query
Posted by Daryl Stultz <da...@6degrees.com>.
On Tue, Jul 6, 2010 at 2:50 PM, Fay Wang <fy...@yahoo.com> wrote:
> Hi,
> Which version of OpenJPA are you using?
Sorry, I'm usually pretty good about including that.
I'm on 1.2.1.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com
Re: Missing table name in query
Posted by Fay Wang <fy...@yahoo.com>.
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 <da...@6degrees.com>
To: OpenJPA User List <us...@openjpa.apache.org>
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:daryl.stultz@opentempo.com