php - SQL 查询中的自定义 ORDER BY 逻辑

Question

我正在寻找以下问题的优雅解决方案:

我目前有一个页面列出条目按日期排序，分页限制。这是我的代码片段:

<?php
$intPageNumber = 1;
if ( isset($this->GET['p']) && (integer)$this->GET['p'] > 1 ) $intPageNumber = (integer)$this->GET['p'];
$strLimit = " LIMIT " . (($intPageNumber-1)*$intItemsPerPage) . ", " . $intItemsPerPage;

$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM jos_h_testimonials ORDER BY date DESC ".$strLimit;
// code to load some records here
// ...

// take care of pagination
$db->setQuery('SELECT FOUND_ROWS()');
$intTotalRecords = $db->LoadResult();
$this->NumberOfPages = ceil( $intTotalRecords / $intItemsPerPage);
$this->CurrentPage = $intPageNumber;
?>

表架构:

CREATE TABLE IF NOT EXISTS `jos_h_testimonials` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `author` varchar(255) NOT NULL,
  `testimonial` text NOT NULL,
  `rating` int(11) NOT NULL,
  `date` int(11) NOT NULL,
  `hgroup_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

这是一种以分页方式处理列表条目的标准方法。我的问题是客户想要特殊排序 - 他们希望具有 hgroup_id 外键特定值的条目出现在顶部。

我能想到的两种解决方案是:

1. 加载整个列表并使用 PHP 重新排序，然后使用 PHP 处理分页；
2. 运行两个查询 - 一个选择具有所需外键的条目，第二个选择所有其他条目。合并结果集并.. 使用 PHP 处理分页。

无论哪种方式，我优雅的单查询 SQL_CALC_FOUND_ROWS 分页方法都行不通。我很好奇我是否可以在 ORDER_BY 子句中使用一些 MySQL“魔法”？

我希望以上是有道理的..

Answer 1

使用简单的 SQL:

SELECT SQL_CALC_FOUND_ROWS *
FROM jos_h_testimonials
ORDER BY
    -- Special case on top
    CASE WHEN hgroup_id = 9 THEN 0
    -- All the rest
    ELSE 1 END ASC,
    -- Regular sort order
    date DESC