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
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
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!
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.
SELECT * FROM post ORDER BY CASE WHEN highlight_date > NOW() THEN 0 ELSE 1 END, post_date DESC LIMIT 20
SELECT *, COALESCE(highlight_date > NOW(),0) highlight FROM post ORDER BY highlight DESC , id DESC
My date NOW is
COALESCE is required for comparing
NULL compared by
date, the result is
0 is Greater than
Wrong Result Without Coalesce:
SELECT *, highlight_date > NOW() highlight FROM post ORDER BY highlight DESC , id DESC