JPQL query to get entities that does not have a child entity in their child entities

Joined on 2015-12-10

I have 2 tables as shown below.

Job Table
ID    JobId    Name     StartTime              FinishTime    ....
01    001      A        2105:12:10 14:00:00    2105:12:10 14:00:10
02    002      A        2105:12:10 14:00:00    2105:12:10 14:00:00
03    003      A        2105:12:10 14:00:00    2105:12:10 14:00:00
04    004      A        2105:12:10 14:00:00    2105:12:10 14:00:00

And

Status Table
ID    Status                Timestamp                JobId
01    Started               2105:12:10 14:00:00      001
02    Step_1_Started        2105:12:10 14:00:00      001
03    Step_1_Finished       2105:12:10 14:00:05      001
04    Step_2_Started        2105:12:10 14:00:05      001
05    Step_2_Finished       2105:12:10 14:00:10      001
06    Finished              2105:12:10 14:00:10      001

07    Started               2105:12:10 14:00:00      002
08    Step_1_Started        2105:12:10 14:00:00      002
09    Step_1_Failed         2105:12:10 14:00:02      002

10    Started               2105:12:10 14:00:00      003
11    Step_1_Started        2105:12:10 14:00:00      003
12    Step_1_Failed         2105:12:10 14:00:02      003
13    Step_1_Canceled       2105:12:10 14:00:04      003

14    Started               2105:12:10 14:00:00      004
15    Step_1_Started        2105:12:10 14:00:00      004

From these 2 tables I have to query for Jobs having states FINISHED, CANCELED, FAILED and ACTIVE.

FINISHED:   A Job having status 'Finished'.
CANCELED: A Job having status '%Canceled' and not ('Finished').
FAILED:      A Job having status '%Failed' and not '%Canceled' and not 'Finished'.
Active:        A Job having status '%Started' and not '%Failed' and not '%Canceled' and not 'Finished'.

I have entities for Job and Status with NamedQueries on Job entity and using TomEE for deployment.

I am using following queries.

FINISHED Jobs between a time interval:
@NamedQuery( name = "findFinishedJobsBetweenTimeIntervals",
  query = "SELECT J FROM JobEntity J, IN(J.statuses) JS WHERE " +
    "J.startTime >=:timeStampBefore AND J.startTime <:timeStampAfter AND " +
    "JS.status = 'Finished'" ),

CANCELED Jobs between a time interval:
@NamedQuery( name = "findCanceledJobsBetweenTimeIntervals",
  query = "SELECT J FROM JobEntity J, IN(J.statuses) JS WHERE " +
    "J.startTime >=:timeStampBefore AND J.startTime <:timeStampAfter AND " +
    "JS.status LIKE '%Failed%' AND JS.status <> 'Finished'" ),

FAILED Jobs between a time interval:
@NamedQuery( name = "findFailedJobsBetweenTimeIntervals",
  query = "SELECT J FROM JobEntity J, IN(J.statuses) JS WHERE " +
    "J.startTime >=:timeStampBefore AND J.startTime <:timeStampAfter AND " +
    "JS.status LIKE '%Failed%' AND JS.status NOT LIKE '%Canceled'" AND JS.status <> 'Finished'" ),

ACTIVE Jobs between a time interval:
@NamedQuery( name = "findActiveJobsBetweenTimeIntervals",
  query = "SELECT J FROM JobEntity J, IN(J.statuses) JS WHERE " +
    "J.startTime >=:timeStampBefore AND J.startTime <:timeStampAfter AND " +
    "JS.status = 'Started' AND JS.status NOT LIKE '%Failed%' AND JS.status NOT LIKE '%Canceled'" AND PS.status <> 'Finished'" ),

With these queries I am getting wrong result for ACTIVE Jobs. A job even with failed or finished state is returned in the active jobs result.

Need help to correct the query.

Thank You.

Joined on 2010-05-03

Could you please demonstrate the issue with a small test case (see these instructions)?

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)

Joined on 2015-12-10

With respect to the above dataset, I expect following results

Active Jobs: 004

However, I get all 001, 002, 003 and 004 as Active Jobs.

Joined on 2010-05-03

Please provide a runnable test case that demonstrates the issue.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)

✂

edit

delete