I'm building a custom news feed component and am trying to implement the concept of highlighted posts. An example of the table is as follows:

Naturally, when pulling data I'll be ordering by post_date DESC, however highlighted posts need to appear at the top, but only if today's date is before the highlighted date. If today's date is after the highlighted date, it it should be ignored and appear naturally within the post_date sort.

This query below for example won't work, as highlighted posts will always appear first, even after the highlight_date has lapsed.

select * from post
order by post_date desc, highlight_date asc
limit 20

While I can separate this into two queries (one for highlighted only and one for regular only), and compound the two in the application, implementing it for use with pagination/infinite scroll will now be difficult as I don't have the benefit of using LIMIT and OFFSET in my query.

Using the above example data, I'm trying to build a query where the resulting order will be [3, 4, 2, 1] for dates before 2021-11-31, but [4, 3, 2, 1] for dates after.

I'm not sure how to go about this in an efficient way, as I'm expecting this table to grow over the years. Any direction or assistance would be greatly appreciated! TIA!

CodePudding user response：

SELECT * FROM post
ORDER BY CASE WHEN highlight_date > NOW() THEN 0 ELSE 1 END, post_date DESC
LIMIT 20

Result when NOW() is greather than highlight_date

Explaination:

COALESCE is required for comparing NULL data. Because when NULL compared by date, the result is NULL. And 0 is Greater than NULL

Wrong Result Without Coalesce:

SELECT  *,
    highlight_date > NOW() highlight
FROM    post
ORDER
BY  highlight DESC , id DESC

Resulted: