Drupal 7使用连接选择查询 [英] Drupal 7 select query with joins

问题描述

我有一种情况，我需要查询一些节点并加入连接到节点的两个字段。这两个字段都可以具有无限值，因此在字段表中有多行。我试图让它返回为这些字段找到的nid和所有的值记录。

  $ 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（））{
 // ... 
} 
  

这样做有效，但实际上还会有aaa_alert_path_value和aaa_alert_region_value返回1条记录。

尝试尝试模块 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（）; 
  

这个工作并将返回我需要的两个字段的所有记录，但是这里有一个错误如果两个字段中的一个不包含记录，则不会返回任何内容。

我尝试使用不同的连接，但我似乎无法正确。我在这里缺少什么？我试图这样做，而不使用 EntityFieldQuery 类，因为它将要求我 node_load 所有返回的结果得到我需要的字段，这是一个巨大的表现。

解决方案

问题存在于 - > groupBy（'n.nid'）

groupBy方法正在将所有记录与相似的节点ID组合成一个结果。删除这将允许每个节点ID的多个结果。请注意，在while循环中，每个字段值将作为单独的记录返回。如果要将它们分组到每个节点的单个数组中，可以执行以下操作：

 <？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']; 
 $ grouping [$ nid] [] = $ record; 
} 
 
 foreach（$分组为$ nid => $ value）{
 // ... 
} 
  

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.

解决方案

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) {
  // ...
}

这篇关于Drupal 7使用连接选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！