You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by Frederic Gedin <fr...@Jaluna.COM> on 2003/03/14 12:03:55 UTC
Criteria quizz
Hello
I am faced with a query problem:
To simplify the problem, I have three OM classes Task Worker,
Assignment) which are generated from the following XML
<table name="XP_TASK" javaName="Task">
<column name = "TASK_ID"
primaryKey = "true"
required = "true"
javaName = "Id"
type = "INTEGER"/>
<column name = "TK_REFERENCE"
javaName = "Reference"
required = "true"
type = "VARCHAR"
size = "32" />
<unique>
<unique-column name="TK_REFERENCE" />
</unique>
</table>
<table name="XP_WORKER" javaName="Worker">
<column name = "WORKER_ID"
primaryKey = "true"
required = "true"
javaName = "Id"
type = "INTEGER"/>
<column name = "WK_FIRST_NAME"
javaName = "FirstName"
required = "true"
type = "VARCHAR"
size = "32" />
<column name = "WK_LAST_NAME"
javaName = "LastName"
required = "true"
type = "VARCHAR"
size = "32" />
<column name = "WK_INITIALS"
javaName = "Initials"
required = "true"
type = "VARCHAR"
size = "3" />
<unique>
<unique-column name="WK_FIRST_NAME" />
<unique-column name="WK_LAST_NAME" />
</unique>
<unique>
<unique-column name="WK_INITIALS" />
</unique>
</table>
<table name="XP_ASSIGNMENT" javaName="Assignment">
<column name = "ASSIGNMENT_ID"
primaryKey = "true"
required = "true"
javaName = "Id"
type = "INTEGER"/>
<column name = "TASK_ID"
required = "true"
type = "INTEGER"/>
<column name = "WORKER_ID"
required = "true"
type = "INTEGER"/>
<foreign-key foreignTable="XP_TASK">
<reference local="TASK_ID" foreign="TASK_ID"/>
</foreign-key>
<foreign-key foreignTable="XP_WORKER">
<reference local="WORKER_ID" foreign="WORKER_ID"/>
</foreign-key>
<unique>
<unique-column name="TASK_ID" />
<unique-column name="WORKER_ID" />
</unique>
</table>
Now I would like to find all the tasks with no assigned worker.
How to do it?
Thank you for your help
Frederic
Re: Criteria quizz
Posted by Frederic Gedin <fr...@Jaluna.COM>.
Dan
Doing it this way is a really good idea. Many thanks
Frederic
Dan Allen wrote:
>>Now I would like to find all the tasks with no assigned worker.
>>
>>How to do it?
>>
>>
>
>Make your life easy and do it in two queries.
>
> Criteria crit = new Criteria();
> crit.addGroupByColumn(AssignmentPeer.TASK_ID);
> List filledTasks = AssignmentPeer.doSelect(crit);
>
> List filledTaskIds = new ArrayList();
> Iterator iter = filledTasks.iterator();
> while (iter.hasNext())
> {
> filledTaskIds.add(new Integer(((Task)iter.next()).getTaskId()));
> }
>
> crit.clear();
> crit.add(TaskPeer.TASK_ID, filledTaskIds, Criteria.NOT_IN);
> List unfilledTasks = TaskPeer.doSelect(crit);
>
>voila!
>
>Dan
>
>
>
Re: Criteria quizz
Posted by Dan Allen <da...@mojavelinux.com>.
> Now I would like to find all the tasks with no assigned worker.
>
> How to do it?
Make your life easy and do it in two queries.
Criteria crit = new Criteria();
crit.addGroupByColumn(AssignmentPeer.TASK_ID);
List filledTasks = AssignmentPeer.doSelect(crit);
List filledTaskIds = new ArrayList();
Iterator iter = filledTasks.iterator();
while (iter.hasNext())
{
filledTaskIds.add(new Integer(((Task)iter.next()).getTaskId()));
}
crit.clear();
crit.add(TaskPeer.TASK_ID, filledTaskIds, Criteria.NOT_IN);
List unfilledTasks = TaskPeer.doSelect(crit);
voila!
Dan
--
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Daniel Allen, <da...@mojavelinux.com>
http://www.mojavelinux.com/
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Rumour has it that bad dreams about Windows can be ended by
putting on a pair of Tux slippers and clicking your heels
together 3 times while saying.
"There's no place like /home."
"There's no place like /home."
"There's no place like /home."
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -