ORM select related entity field

Collapse
X
 
  • Time
  • Show
Clear All
new posts

  • telecastg
    commented on 's reply
    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
    replied
    Everything clear, Thank you Yuri

    Leave a comment:


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

    Contribute to o-data/EspoCRM-Learning-and-Design development by creating an account on GitHub.

  • yuri
    replied

    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:


  • Ceonello
    replied
    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

    Leave a comment:


  • item
    commented on 's reply
    Thanks Yuri

  • telecastg
    commented on 's reply
    Thanks for providing well commented code examples yuri ! they really help to understand and apply the information provided by the official documentation

  • yuri
    replied
    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:


  • Ceonello
    started a topic ORM select related entity field

    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(); 
    
    This code just return the case name and not the contact name, am I doing something wrong?
    Thank you
Working...