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

1
0
-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 answers

1
0
-1
This one is the BEST answer!

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

Comments

Submitted by nitikakhatkar97... on Wed, 02/19/2020 - 09:29

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

Submitted by nitikakhatkar97... on Wed, 02/19/2020 - 09:44

This query is possible in community version as well?

Submitted by antoine.mottier on Wed, 02/19/2020 - 09:49

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.

Submitted by nitikakhatkar97... on Wed, 02/19/2020 - 09:53

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.

Submitted by nitikakhatkar97... on Wed, 02/19/2020 - 09:53

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.

1
0
-1

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

Submitted by peershariff on Wed, 07/12/2017 - 10:00

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.

Submitted by chris.lowth on Fri, 07/14/2017 - 16:24

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.

Notifications