I have application using Mysql Database with this schema for example.
packages
id, name, order
1, 'Free', 3
2, 'Basic', 2
3, 'Extensive', 1
ads
id, date, package_id, package_expires_at
1, '2021-12-10 00:00:00', 1, '2022-01-10'
2, '2021-12-15 00:00:00', 3, '2021-12-28'
3, '2021-12-15 00:00:00', 3, '2022-01-10'
4, '2021-12-20 00:00:00', 2, '2022-01-10'
5, '2021-12-21 00:00:00', 1, '2021-12-28'
6, '2021-12-25 00:00:00', 1, '2021-12-28'
What I need to achieve is selecting all ads but ordering none expired package first by packages.order asc, package_expire_at desc
then order the expired by date desc
for example suppose we are in '2021-12-29' then the result should be
id, date, package_id, package_expires_at
3, '2021-12-15 00:00:00', 3, '2022-01-10'
4, '2021-12-20 00:00:00', 2, '2022-01-10'
1, '2021-12-10 00:00:00', 1, '2022-01-10'
6, '2021-12-25 00:00:00', 1, '2021-12-28'
5, '2021-12-21 00:00:00', 1, '2021-12-28'
2, '2021-12-15 00:00:00', 3, '2021-12-28'
I have added the schema on fiddle
http://sqlfiddle.com/#!9/cedb45/9
CodePudding user response:
The way you do this in (My)SQL is, you provide a virtual field as ORDER BY
condition.
Something like this should probably do the trick:
SELECT a.id
, a.date
, a.package_id
, a.package_expires_at
, IF( a.package_expires_at < CURRENT_DATE, 1, 0 ) AS is_expired
, IF( a.package_expires_at < CURRENT_DATE, a.`date`, a.`package_expires_at` ) AS sort_date
, p.`order` AS sort_package_order
FROM ads AS a
INNER JOIN packages AS p
ON p.id = a.package_id
ORDER BY IF( a.package_expires_at < CURRENT_DATE, 1, 0 ) ASC
, IF( a.package_expires_at < CURRENT_DATE, 1, p.`order` ) ASC
, IF( a.package_expires_at < CURRENT_DATE, a.`date`, a.`package_expires_at` ) DESC
CodePudding user response:
use two separate query and then union
them (your packages order is upside down of its ids now)
select * from (SELECT * from ads WHERE package_expires_at >= '2021-12-29' order by package_id asc, package_expires_at desc ) as a
union
select * from (SELECT * from ads WHERE package_expires_at < '2021-12-29' order by date desc) as b
if they are dynamic and without order, join them and then do things such as above .
select * from (SELECT ads.id,date,package_id,package_expires_at from ads left outer join packages on package_id = ads.id WHERE package_expires_at >= '2021-12-29' order by packages.order asc, package_expires_at desc ) as a
union
select * from ( SELECT ads.id,date,package_id,package_expires_at from ads left outer join packages on package_id = ads.id WHERE package_expires_at < '2021-12-29' order by date desc ) as b