I'm trying to add this thread to the "Wiki", any recommendation on the title? Not quite sure what it is
ORM select related entity field
Collapse
X
-
You can to use the query builder.
PHP Code:$query = $entityManager ->getQueryBuilder() ->select() ->from('Case', 'case') // table name in CamelCase and alias ->select('emailAddress.name', 'contactEmailAddress') // expression and alias ->join( 'Contact', 'contact', [ 'contact.id:' => 'case.id', 'contact.deleted' => false, ] ) ->leftJoin( 'EntityEmailAddress', 'entityEmailAddress', [ 'entityEmailAddress.entityId:' => 'contact.id', 'entityEmailAddress.entityType' => 'Contact', 'entityEmailAddress.deleted' => false, 'entityEmailAddress.primary' => true, // join only primary, a record can have multiple email addresses ] ) ->leftJoin( 'EmailAddress', 'emailAddress', [ 'emailAddress.id:' => 'entityEmailAddress.emailAddressId', ] ) ->build();
Last edited by yuri; 03-22-2021, 09:31 AM.Leave a comment:
-
Hi Yuri,
thank you very much for the very clear and useful explanation.
Just another question:
What if I need a field that is not an attribute of the Case entity, for example the Contact email address. Can I do it with a single query or I need to query the contact entity for every Case returned by the parent query?
Thank youLeave a comment:
-
Hi,
When you use a repository you can use only defined fields (we use 'attribute' term). The Case entity does not have an attribute 'contact.name'. But it does have 'contactName' attribute as a part of 'contact' field.
PHP Code:$caseCollection = $entityManager ->getRepository('Case') ->select([ 'name', ['contact.name', 'contactName'], // contactName will be used as an alias in query (contact.name AS 'contactName') ]) ->join('contact') ->find(); foreach ($caseCollection as $case) [ $contactName = $case->get('contactName'); }
or
PHP Code:$caseCollection = $entityManager ->getRepository('Case') ->select([ 'name', 'contactName', ]) ->find(); // left join will be applied automatically, since the foreign field 'contactName' is in SELECT foreach ($caseCollection as $case) [ $contactName = $case->get('contactName'); }
or w/o using a repository
PHP Code:$query = $entityManager ->getQueryBuilder() ->select() ->from('Case') ->select('name') // select method can by called multiple times, it adds items to SELECT list ->select('contact.name', 'contactName') // expression and alias ->join('contact') // joining by relation name, join condition will be applied automatically ->build(); $sth = $entityManager->getQueryExecutor()->execute($query); while ($row = $sth->fetch(\PDO::FETCH_ASSOC)) { $contactName = $row['contactName']; }
or using an explicit join
PHP Code:$query = $entityManager ->getQueryBuilder() ->select() ->from('Case') ->select('name') ->select('contact.name', 'contactName') ->join( 'Contact', // table name in CamelCase 'contact', // alias [ 'contact.id:' => 'id', // colon indicates that the right part is an expression rather than a value 'contact.deleted' => false, ] ) ->build();
Last edited by yuri; 03-21-2021, 04:02 PM.Leave a comment:
-
ORM select related entity field
Hi all,
I'm creating a custom rest service to retrieve some data from the system.
What i would like to do is to return a case list with some related entities fields, let's suppose for example that i want to return the case name and the related contact name:
PHP Code:$cases = $entityManager->getRepository('Case') ->select() ->select(['name', 'contact.name']) ->join("contact") ->find();
Thank youTags: None
Leave a comment: