ORM select related entity field

[Ceonello]

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:

$cases = $entityManager->getRepository('Case')
->select()
->select(['name', 'contact.name'])
->join("contact")
->find(); 

This code just return the case name and not the contact name, am I doing something wrong?
Thank you

[yuri]

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.

$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

$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

$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

$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(); 

[item]

Thanks Yuri

[espcrm]

I'm trying to add this thread to the "Wiki", any recommendation on the title? Not quite sure what it is

https://github.com/o-data/EspoCRM-Learning-and-Design/wiki

[telecastg]

I'm trying to add this thread to the "Wiki", any recommendation on the title? Not quite sure what it is

I would suggest "Using ORM to build queries" or "ORM queries explained"

[Ceonello]

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 you

[yuri]

You can to use the query builder.

$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(); 

[Ceonello]

Everything clear, Thank you Yuri