How do I query entities to get all fields values - including referenced ones (INNER JOIN)

Question

I have custom content types in my Drupal 8 module and want to query nodes of those content types programmatically.

Querying through entityQuery is easy. However, I have also entity reference fields (so a node of one content type has a relation to a node of another content type).

Is there a way how to make a query that will return 'rows' of field values, exactly as when I use raw SQL INNER JOIN? I want to have an associative array of all field values, not only the specific node, but also the referenced nodes/entities.

Right now I have an own class to handle this, but it's way too much complicated and, I believe, unnecessary, so I guess I'm missing something.

Thank you.

Answer 1

Views uses joined tables a lot, you can implement this in UI and run it in code.

Or use a db_select.

Example from Drupal\taxonomyTermStorage:

public function getNodeTerms(array $nids, array $vocabs = array(), $langcode = NULL) {
    $query = db_select('taxonomy_term_field_data', 'td');
    $query->innerJoin('taxonomy_index', 'tn', 'td.tid = tn.tid');
    $query->fields('td', array('tid'));
    $query->addField('tn', 'nid', 'node_nid');
    $query->orderby('td.weight');
    $query->orderby('td.name');
    $query->condition('tn.nid', $nids, 'IN');
    $query->addTag('term_access');
    if (!empty($vocabs)) {
      $query->condition('td.vid', $vocabs, 'IN');
    }
    if (!empty($langcode)) {
      $query->condition('td.langcode', $langcode);
    }

    $results = array();
    $all_tids = array();
    foreach ($query->execute() as $term_record) {
      $results[$term_record->node_nid][] = $term_record->tid;
      $all_tids[] = $term_record->tid;
    }

    $all_terms = $this->loadMultiple($all_tids);
    $terms = array();
    foreach ($results as $nid => $tids) {
      foreach ($tids as $tid) {
        $terms[$nid][$tid] = $all_terms[$tid];
      }
    }
    return $terms;
  }

Answer 2

No, there is not.

Entity query only returns IDs, you need to load those and then from there access the referenced entities.