Example: Querying tasks in the claimed state

The user, John Smith, wants to search for tasks that he has claimed and are still in the claimed state. The condition that specifies claimed by John Smith is TASK.OWNER = 'JohnSmith'. The following code snippet shows the query method call for the query:

query( "DISTINCT TASK.TKIID", 
           "TASK.STATE = TASK.STATE.STATE_CLAIMED AND " +  
           "TASK.OWNER = 'JohnSmith'", 
           (String)null, (String)null, (Integer)null, (TimeZone)null )

The following code snippet shows the SQL statement that is generated from the API query:

SELECT DISTINCT TASK.TKIID
   FROM   TASK TA, WORK_ITEM WI,  
   WHERE  WI.OBJECT_ID = TA.TKIID
   AND    TA.STATE = 8
   TA.OWNER = 'JohnSmith'                            
   AND  ( WI.OWNER_ID = 'JohnSmith' OR WI.OWNER_ID = null AND WI.EVERYBODY = true ) 
    

When a task is claimed, work items are created for the owner of the task. So, an alternative way of forming the query for John Smith's claimed tasks is to add the following condition to the query instead of using TASK.OWNER = 'JohnSmith':

WORK_ITEM.REASON = WORK_ITEM.REASON.REASON_OWNER

The query then looks like the following code snippet:

query( "DISTINCT TASK.TKIID", 
           "TASK.STATE = TASK.STATE.STATE_CLAIMED AND " +  
           "WORK_ITEM.REASON = WORK_ITEM.REASON.REASON_OWNER", 
           (String)null, (String)null, (Integer)null, (TimeZone)null )

The following actions are taken when the SQL SELECT statement is generated:

• A condition for access control is added to the where clause. This example assumes that group work items are not enabled.
• Constants, such as TASK.STATE.STATE_READY, are replaced by their numeric values.
• A FROM clause and join conditions are added.

The following code snippet shows the SQL statement that is generated from the API query:

SELECT DISTINCT TASK.TKIID
   FROM   TASK TA, WORK_ITEM WI,  
   WHERE  WI.OBJECT_ID = TA.TKIID
   AND    TA.STATE = 8
   AND    WI.REASON = 4                                
   AND  ( WI.OWNER_ID = 'JohnSmith' OR WI.OWNER_ID = null AND WI.EVERYBODY = true ) 
    

John is about to go on vacation so his team lead, Anne Grant, wants to check on his current work load. Anne has system administrator rights. The query she invokes is the same as the one John invoked. However, the SQL statement that is generated is different because Anne is an administrator. The following code snippet shows the generated SQL statement:

SELECT DISTINCT TASK.TKIID
   FROM   TASK TA, WORK_ITEM WI,  
   WHERE  TA.TKIID = WI.OBJECT_ID =  
   AND    TA.STATE = 8                            
   AND   TA.OWNER = 'JohnSmith')

Because Anne is an administrator, an access control condition is not added to the WHERE clause.