[prev in list] [next in list] [prev in thread] [next in thread]
List: ojb-user
Subject: Joining a subquery to parent across non-decomposed m:n
From: Steve_Clark () fws ! gov
Date: 2005-09-22 22:34:45
Message-ID: OF8F4C2C1C.51F02F25-ON87257084.00782A8E-87257084.007C0917 () fws ! gov
[Download RAW message or body]
I have a query that I can't figure out how to write. I'm using 1.0.4 and
PersistenceBroker. Here's my situation:
Project has a Collection of Activities; this is a non-decomposed m:n via
ACTIVITY_PROJECT.
Activity has attribute 'conclusionDate'.
I want to find all Projects for which every Activity has a non-null
conclusionDate. In SQL, here's the query I'm aiming for:
SELECT DISTINCT A0.PROJECT_ID, count(A2.ACTIVITY_ID)
FROM PROJECT A0
INNER JOIN ACTIVITY_PROJECT A1 ON A0.PROJECT_ID=A1.PROJECT_ID
INNER JOIN ACTIVITY A2 ON A1.ACTIVITY_ID=A2.ACTIVITY_ID
WHERE NOT EXISTS
(SELECT B0.ACTIVITY_ID FROM ACTIVITY B0, ACTIVITY_PROJECT B1
WHERE B0.ACTIVITY_ID=B1.ACTIVITY_ID
AND B1.PROJECT_ID=A1.PROJECT_ID
AND B0.CONCLUSION_DATE IS NULL);
Here's pseudocode for what I'm trying to do:
subCrit = conclusionDate is not null
// This is the problematic line - how to join to the parent query?
subCrit.addEqualToField("activityId", Criteria.PARENT_QUERY_PREFIX
+ "activityId")
ReportQueryByCriteria subQuery =
QueryFactory.newReportQuery(Activity.class, subCrit);
subQuery.setAttributes({ "activityId" });
crit = new Criteria();
crit.addNotExists(subQuery);
What I can't figure out is how to get the join right in the subquery.
Any suggestions?
thanks,
-steve
Steve Clark
ECOS Development Group
steve_clark@fws.gov
(970)226-9291
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic