使用$ query-> addExpression（）时如何保留要选择的字段的顺序 [英] How to preserve the order of the fields to be selected when using $query->addExpression()

问题描述

我使用的是Drupal 7，我必须在多个表上进行联合。对于工会工作，必须满足一些条件：

I'm using Drupal 7 and I have to make a union on multiple tables. For the union to work some conditions have to be met:

1. 相同数量的列


2. same数据类型


3. 相同订单

1. same number of columns
2. same data type
3. same order

缺少一个列，所以为了补偿，我只是添加它与这样的东西： $ query-> addExpression（'：field_1'，'field_1'，array（'：field_1'=> NULL））; 。所以在这个时候条件1& 2满足但选择中的字段顺序是不同的。

Some of the tables are missing a column so in order to compensate for that I just add it with something like this: $query->addExpression(':field_1', 'field_1', array(':field_1' => NULL));. So at this point condition 1 & 2 are satisfied, but the order of the fields in the select is different.

请参见下面的示例：

  $query_1 = db_select('table_one', 't1');
  $query_1->fields('t1', array('field_1', 'field_2'));

  $query_2 = db_select('table_two', 't2');
  if (true) {
    $query_2->fields('t2', array('field_1'));
  } else {
    $query_2->addExpression(':field_1', 'field_1', array(':field_1' => NULL));
  }
  $query_2->fields('t2', array('field_2'));        

  $query_3 = db_select('table_three', 't3');
  if (false) {
    $query_3->fields('t3', array('field_1'));
  } else {
    $query_3->addExpression(':field_1', 'field_1', array(':field_1' => NULL));
  }
  $query_3->fields('t3', array('field_2'));

结果是：

// dpq($query_1);
SELECT t1.field_1 AS field_1, t1.field_2 AS field_2
FROM {table_one} t1

// dpq($query_2);
SELECT t2.field_1 AS field_1, t2.field_2 AS field_2
FROM {table_two} t2

// dpq($query_3);
SELECT t3.field_2 AS field_2, '' AS field_1
FROM {table_three} t3

// dpq($query_1->union($query_2)->union($query_3));
SELECT t1.field_1 AS field_1, t1.field_2 AS field_2
FROM {table_one} t1 
UNION SELECT t2.field_1 AS field_1, t2.field_2 AS field_2
FROM {table_two} t2 
UNION SELECT t3.field_2 AS field_2, '' AS field_1
FROM {table_three} t3

看起来使用 $ query-> addExpression 不会保证该字段的位置是您期望的位置。看看第三个查询的转储已经使用了 addExpression 。关于如何解决这个问题的任何想法？

Seems that using $query->addExpression will not guarantee the position of the field to be where you'd expect it to be. Look at the dump of the 3rd query where addExpression has been used. Any ideas on how to solve this issue?

推荐答案

这是我发现保持顺序的唯一解决方法。即使该字段存在或不存在，也可以使用表达式。这样的顺序就是你所期待的那样：

This is the only workaround I found to keep the order. Use an expression even if that field exists or not. That way the order is the one that you'd expect:

if (TRUE) {
  // To keep the order of the fields we have to use this hacky way.
  // Use a function that will not modify the string
  $query->addExpression("IFNULL(table_name.field_name, '')", 'field_name_alias');
} else {
  $query->addExpression(':field', 'field_name_alias', array(':field' => NULL));
}

这篇关于使用$ query-> addExpression（）时如何保留要选择的字段的顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！