What is the correct BDM Custom Query?
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,
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
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.
Comments
Hi,
Have you tried to implement those queries and get any errors?
If yes, could you share the error message you get?
Captain Bonita
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