Custom ORDER BY logic in an SQL query

Question

I'm looking for an elegant solution to the following problem:

I currently have a page listing entries ORDER BY date, with a pagination LIMIT. Here's a snippet of my code:

<?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;
?>

Table schema:

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`)
)

This is quite a standard way of handling listing entries in a paged fashion. My problem is that the client wants to special ordering - they wish to have entries with a certain value for the hgroup_id foreign key to appear at the top.

The two solutions I can think of are:

Load the entire list and re-order it using PHP, then handle pagination using PHP;
Run two queries - one to select entries with a desired foreign key, the second one to select all other entires. Merge result sets and.. handle pagination using PHP.

Either way, my elegant single-query SQL_CALC_FOUND_ROWS approach to pagination won't work. I was curious if there's some MySQL "magic" that I could employ in the ORDER_BY clause?

I hope the above makes sense..

The description isn't complete. What's the table schema (as `CREATE TABLE` statement)? What's the exact condition (in English, if you're not sure of the SQL) for the rows that should appear first? — outis, Nov 25 '11 at 11:56
As outis says, it is not clear from your description why you can't just sort by this foreign key in the query to put them at top. — Cylindric, Nov 25 '11 at 12:04
@Cylindric The reason why I can't ORDER BY hgroup_id is because it's not ASC nor DESC ordering - I may need to show all records with say hgroup_id = 9 at the top, and then the rest of records ordered by date below. — Val Redchenko, Nov 25 '11 at 12:19
Please put the table schema in your question so everybody can see it without looking at the comments. — CodeZombie, Nov 25 '11 at 12:32

score 3 · Accepted Answer · answered Nov 25 '11 at 12:25

3

Use simple 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

answered Nov 25 '11 at 12:25

CodeZombie

5,367
3
30
37

Thank you @ZombieHunter - I suspected something like this was possible. I'm grateful I didn't have to write an abomination – Val Redchenko Nov 25 '11 at 13:00
Why not just "ORDER BY hgroup_id = 9 DESC"? – Cylindric Nov 25 '11 at 13:38

score 1 · Answer 2 · answered Nov 25 '11 at 11:54

1

It's not terribly elegant, but you could use UNION to combine the result of two sub-selects, which means MySQL would still handle pagination. However, it wouldn't be very performant, since MySQL would still need to fetch the entirety of each result.

answered Nov 25 '11 at 11:54

outis

75,655
22
151
221

Custom ORDER BY logic in an SQL query

2 Answers2