gistfile1.txt

    $query = $this->database->select('node_field_data', 'n');
    $query->addField('n', 'nid', 'id');
    $query->addField('n', 'title', 'organisation_name');
    $query->addField('n', 'created');

    $query->leftJoin('node__field_postal_address', 'pa', 'n.nid = pa.entity_id');
    $query->leftJoin('location', 'l', 'l.id = pa.field_postal_address_target_id');
    $query->fields('l', [
      'name',
      'address',
      'contact_name',
      'contact_number',
      'mobile',
      'email',
      'url',
    ]);

    $query->leftJoin('node__field_submitter_name', 'sn', 'sn.entity_id = n.nid');
    $query->addField('sn', 'field_submitter_name_value', 'submitter_name');

    $query->leftJoin('node__field_submitter_email', 'se', 'se.entity_id = n.nid');
    $query->addField('se', 'field_submitter_email_value', 'submitter_email');

    $query->leftJoin('node__field_directory_suburb', 'ds', 'ds.entity_id = n.nid');
    $query->leftJoin('taxonomy_term_field_data', 'ts', 'ts.tid = ds.field_directory_suburb_target_id');
    $query->addField('ts', 'name', 'suburb');

    $query->condition('n.type', 'directory');

    $directories = $query->execute()->fetchAllAssoc('id');

    foreach ($directories as $directory) {
      $query2 = $this->database->select('node__field_directory_type', 'dt');
      $query2->addField('dt', 'field_directory_type_target_id', 'id');

      $query2->leftJoin('taxonomy_term_field_data', 'tt', 'tt.tid = dt.field_directory_type_target_id');
      $query2->addField('tt', 'name');

      $query2->condition('dt.entity_id', $directory->id);
      $types = $query2->execute()->fetchAllAssoc('id');

      kint($types);
    }