Drupal db_select PDO 语句:ORDER BY CASE

2022-01-23 00:00:00 php mysql pdo drupal drupal-7

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

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

MySQL 示例:

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

我现在尝试在 Drupal 中使用其 PDO 风格的 db_select() 函数重新创建此语句.但是我在写 ORDER BY 子句的时候卡住了.

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 示例:

 $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();

谁能指出我如何编写 ORDER BY 部分的正确方向?

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

推荐答案

我认为您可以通过在 select 语句中添加一个表达式来做到这一点，然后将该字段添加为 order by 字段.仅当您可以在选择列表中包含额外字段时，这才有效.这将像这样工作:

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

我认为您不能在 orderBy 子句中专门使用表达式，但这应该适合您.

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

相关文章