What is the correct BDM Custom Query?

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

Comments

Submitted by nicolas.chabanoles on Fri, 10/30/2020 - 14:04

Hi,

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

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

Captain Bonita

Submitted by Pierre-yves Monnet on Sat, 10/31/2020 - 02:14

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

2 answers

1
+2
-1
This one is the BEST answer!

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

1
0
-1

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.

Notifications