Announcement

Collapse
No announcement yet.

ORM select related entity field

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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.

    Comment


    • item
      item commented
      Editing a comment
      Thanks Yuri

    • esforim
      esforim commented
      Editing a comment
      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.

    • telecastg
      telecastg commented
      Editing a comment
      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"

  • #3
    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

    Comment


    • #4

      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.

      Comment


      • #5
        Everything clear, Thank you Yuri

        Comment

        Working...
        X