使用 $query->addExpression() 时如何保留要选择的字段的顺序

2022-01-23 00:00:00 mysql drupal-7 dynamic-queries

我正在使用 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. 相同的数据类型
  3. 相同的顺序

有些表缺少一列，所以为了弥补这一点，我只添加了如下内容:$query->addExpression(':field_1', 'field_1', array(':field_1' => NULL));.所以此时条件 1 &2个都满意，但是select中的字段顺序不同.

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));
}

相关文章