custom query - how can we fetch the set of records based on BDM type attribute in custom query ?

Q&A
1

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

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.

3

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
4

Thanks for replying…

I am using a separate table because it will be helpful while preparing a report based on employeeType. instead of user entering the text he will select it from the dropdown, other wise the user may enter text with spelling mistakes.

5

Ok - but you can populate a dropdown of fixed options in a form without having to hold the options in a table. If you want them to be configurable there are plenty of ways of doing that without adding complexity to the DB definition.

6

I have tried to use this syntax for joining two tables but it is returning null even when there is matching.

7

This query is possible in community version as well?

8

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.

9

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.

10

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.