CODEWITHSUNDEEP
mysqldrupal
Colton Echols
Colton Echols

Reputation: 3

Drupal 7 select query with joins

I have a situation where I need to query some nodes and join two of the fields attached to the node. These two fields both can have unlimited values so there are multiple rows in the field tables. I am trying to get it to return the nid and all value records found for the fields.

  $query = db_select('node', 'n');
  $query->leftJoin('field_data_aaa_alert_path', 'ap', 'n.nid = ap.entity_id');
  $query->leftJoin('field_data_aaa_alert_region', 'ar', 'n.nid = ar.entity_id');
  $query
    ->fields('n', array('nid'))
    ->fields('ap', array('aaa_alert_path_value'))
    ->fields('ar', array('aaa_alert_region_value'))
    ->groupBy('n.nid')
    ->condition('type', 'aaa_alert')
    ->condition('status', 1)
    ->orderBy('created', 'DESC');

  $result = $query->execute();
  while($record = $result->fetchAssoc()){
    //...
  }

This works, but it will only return 1 record for aaa_alert_path_value and aaa_alert_region_value when in fact there are more.

I then attempted to try the module EntityFieldQuery Extra Fields

    $query = new EntityFieldQueryExtraFields();
    $query->entityCondition('entity_type', 'node')
        ->entityCondition('bundle', 'aaa_alert')
        ->propertyCondition('status', NODE_PUBLISHED)
        ->addExtraField('aaa_alert_region', 'value')
        ->addExtraField('aaa_alert_path', 'value')
        ->propertyOrderBy('created', 'DESC');

    $result = $query->execute();

This works and will return all the records for the two fields I need but there is a bug in this module that wont return anything if one of the two fields doesn't contain a record.

I've tried using different joins but I cant seem to get it right. What am I missing here? I'm trying to do this without using the EntityFieldQuery class because It will require me to node_load all the results returned to get the fields I need and that is a huge performance hit.

Upvotes: 0

Views: 918

Answers (1)

ScottA
ScottA

Reputation: 1811

The problem exists with ->groupBy('n.nid')

What the groupBy method is doing is combining all records with a similar node ID into one result. Removing this will allow multiple results for each node ID. Take note that within the while loop, each field value will be returned as a separate record. If you want to group them into a single array per each node, you could do something like this:

<?php

$query = db_select('node', 'n');
$query->leftJoin('field_data_aaa_alert_path', 'ap', 'n.nid = ap.entity_id');
$query->leftJoin('field_data_aaa_alert_region', 'ar', 'n.nid = ar.entity_id');
$query
  ->fields('n', array('nid'))
  ->fields('ap', array('aaa_alert_path_value'))
  ->fields('ar', array('aaa_alert_region_value'))
  ->condition('type', 'aaa_alert')
  ->condition('status', 1)
  ->orderBy('created', 'DESC');

$result = $query->execute();
while($record = $result->fetchAssoc()){
  $nid = $result['nid'];
  $grouped[$nid][] = $record;
}

foreach($grouped as $nid => $value) {
  // ...
}

Upvotes: 1

Related Questions