Using CASE to Sort Content
Sort Items Based on Category Settings
I have a simple content management back-end system that all of our clients use for keeping their web sites up to date. I say back end, because we still build very custom front ends for the clients. One of the features it has is in the category settings, the client can choose from a list of ways to sort the content in that category. Each category may have totally different types of content and require different sorting methods. A news category will most likely be sorted by date, newest first; while a category that contains bios for a company will need to be sorted in and arbitrary order of the clients choice.
To accomplish this the category table has a column called sortContentBy. The value is an integer representing different sort methods. For this example, I will use the two I already mentioned. If the value is 0, we will sort by 'content_datetime'. If the value is 1 we will sort by our arbitrary sort order, a column called .sort'
The trick for me was to get this to work with a single query, and still have the proper sort orders PER category. There was no way I was going to allow myself to run a query to get the category information, then loop it and run another query to get the content info. Not gonna happen...
With the help of SQL genius Rudy over at http://www.r937.com I had a query up and running to do just so. If you CAST the CASE expressions to the same data type, you can use a single CASE expression I sort by a combination of integer, datetime, and char. I had less trouble getting the ORDER BY to work properly by using separate CASE statements.
The following query will return all of our content data sorted as specified in the category table.
SELECT
c.id
,c.title
,c.content
,cat.title AS catTitle
FROM module_content_category AS cat
LEFT JOIN module_content AS c
ON c.category_id = cat.id
WHERE c.published = 1
ORDER BY cat.title,
CASE
WHEN cat.sortContentBy = 0 THEN c.content_date
END DESC,
CASE
WHEN cat.sortContentBy = 1 THEN c.sort
END ASC
____________
