Drupal db_select PDO语句：ORDER BY CASE [英] Drupal db_select PDO Statement: ORDER BY CASE

问题描述

你好我写了一个MySQL语句，它有一个条件order by子句，你可以在下面的例子中看到。

MySQL示例：

  SELECT 
 title，
 description 
 FROM books 
 WHERE 
 title LIKE％keyword％或描述LIKE％keyword％
 ORDER BY 
 CASE 
 WHEN标题LIKE％keyword％
 THEN 1 
 ELSE 2 
 END 
  

我正在尝试使用其PDO样式在Drupal中重新创建此语句db_select（）函数。但是在写ORDER BY子句的时候我已经停了。

Drupal示例：

  $ node_select = db_select（'node'，'n'）; 
 $ node_select-> fields（'n'，array（'title'，'description'））; 
 $ node_select-> condition（
 db_or（） - > condition（'n.title'，'％'。$ keyword。'％'，'LIKE'）
  - > ;条件（'n.description'，'％'。$ keyword。'％'，'LIKE'）
）; 
 $ node_select-> order（）; 
  

任何人都可以指出正确的方向，您将如何编写ORDER BY部分？

解决方案

我想您可以通过在select语句中添加表达式，然后将该字段添加为按字段排序。这将只有在您选择列表中有额外的字段的情况下才有效。这样做可以这样工作：

  $ query-> addExpression（'CASE WHEN title LIKE％keyword％THEN 1 ELSE 2 END'，'order_col'）; 
 $ query-> orderBy（'order_col'，'ASC'）; 
  

我不认为你可以在orderBy子句中特别使用表达式，但这应该适合你。

Hi I have written a MySQL statement that has a conditional order by clause which you can see in the example below.

MySQL Example:

SELECT 
    title, 
    description
FROM books
WHERE 
    title LIKE "%keyword%" OR description LIKE "%keyword%"
ORDER BY 
    CASE 
        WHEN title LIKE "%keyword%"
        THEN 1
        ELSE 2
     END

I am now trying to recreate this statement in Drupal using its PDO style db_select() function. But I have got stuck when writing the ORDER BY clause.

Drupal Example:

 $node_select = db_select('node', 'n');
 $node_select->fields('n', array('title', 'description'));
 $node_select->condition(
     db_or()->condition('n.title', '%'.$keyword.'%', 'LIKE')
            ->condition('n.description', '%'.$keyword.'%', 'LIKE')
 );
 $node_select->order();

Can anyone point me in the right direction on how you would write the ORDER BY section?

解决方案

I think you can do this by adding an expression in the select statement, then adding that field as the order by field. This will only work if you are ok having the extra field in the select list. This would work something like this:

$query->addExpression('CASE WHEN title LIKE "%keyword%" THEN 1 ELSE 2 END', 'order_col');
$query->orderBy('order_col', 'ASC');

I don't think you can specifically use expressions in the orderBy clause, but this should work for you.

这篇关于Drupal db_select PDO语句：ORDER BY CASE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！