What is the correct BDM Custom Query?

Q&A
1

Hello,

Here my BDM (3 objects)

Employee <-1:1 -> Contact (name)

Employee <1-n> Phone (phoneNumber)

Request 1 :

I want to find an EMPLOYE from a CONTACT.NAME

CustomQuery on EMPLOYEE

Is the request is: select e from EMPLOYEE e where e.contact.name = :name ?

Request 2 :

search an EMPLOYE form a Phone Number

CustomQuery on EMPLOYEE

Is the request is:

select e from EMPLOYEE e where e.phone.phonenumber = :phonenumber?

of (source Wikipedia https://en.wikibooks.org/wiki/Java_Persistence/JPQL#Aggregation_functions)

SELECT e FROM Employee e JOIN e.phone p WHERE p.phonenumber = :phonenumber

Request 3

Search all phones attached to a Employee

CustomQuery on PHONE

Select p from PHONE left join EMPLOYEE e.phone where p.phonenumber = :phonenumber

?

Thank you,

 

 

2

Hello Pierre-Yves,

As you said this type of query should better be done via a direct SQL request because it is not possible to do it via the BDM.

 

3

Hello, 

Thanks Marielle! Together, after multiple tries, we found (in 7.11, seem not worked before)

Query to search by the contact (1-1 relation) : SELECT e FROM Employee e WHERE e.contact.lastName = :lastname

Query to search by the phone number (1-n relation) : Select e from Employee  e JOIN e.phones p where p.phoneNumber = :phonenumber

Example is here

https://www.dropbox.com/s/50fnku9ddbdk1am/PopulateEmployee-1.0.bos?dl=0

4

Hi,

Have you tried to implement those queries and get any errors?

If yes, could you share the error message you get?

Captain Bonita

5

Hello,

Sorry this question was too old. I announce it is not possible to do this kind of request via the BDM, and we moved to a direct SQL request.

If I have time, I will rebuild the use case (two BDM object).

Thank for your time, Captain

6

Hello Delphine, well, if this is works in JPQL, so it's native in Bonita, it's better to use it. Else, if you want to do via a direct SQL, there is nothing native in Bonita, except the Gasoline Page available in the Community.

 

7

hello sir;

I can't open it in community version, could you make a community version?