Select news posts from a database with a highlighted date posts sorted first

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!

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

db<>fiddle

Method 2

SQL:

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

My date NOW is 2021-10-01 23:06:00

Result when NOW() is lower than highlight_date

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:

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0