custom query - how can we fetch the set of records based on BDM type attribute in custom query ?
i have two business objects as follows
employee - name String
- eType employeeType
employeeType - name String.
custom query :- SELECT e
FROM employee e
WHERE e.eType.persistenceId = :id
ORDER BY e.persistenceId ASC
the above query is not returning any result, i am using version 7.5.2
2 answers
Actually the issue is I think in your query syntax. Actually as Employee and EmployeeType are two different tables you need to have a join (AFAIK, I'm not a JPQL expert).
Here is a version that I think should do what you want:
SELECT e
FROM Employee e
JOIN e.eType type
WHERE type.persistenceId = :inputId
ORDER BY e.persistenceId ASC
Why are you using a separate table "employeeType" just to hold a list of strings? I'd make the "eType" field of "employee" a string, and then it's simple.
Comments
I have tried to use this syntax for joining two tables but it is returning null even when there is matching.
This query is possible in community version as well?
Custom queries using JPQL is also available in Community Edition. Note that I'm not sure that JOIN operation is supported in JPQL in Bonita it need to be tested.
Can u test it once and tell me because I have tried the same and got null output even when there are few rows which match.
Can u test it once and tell me because I have tried the same and got null output even when there are few rows which match.