In my table payments I handle both payments users have made & trials. Trials are set as paid but the total is zero (0.00$), like:
ID | Subscription | Plan | Total | Paid | Paid at | Expires at
1 1 1 0.00 1 null 2020-11-05
When the trial is about to expire, the user is forced to purchase a plan, the following row is created:
ID | Subscription | Plan | Total | Paid | Paid at | Expires at
1 1 1 0.00 1 null 2020-11-05
2 1 1 15.00 1 2020-11-06 2020-12-06
As you can see the total is no longer zero and the variable paid_at is also filled. The SQL code should read the row number 2.
What I need now is to verify if the last payment is expired. But before we proceed, keep in mind the following: the user may choose to purchase a plan but don't pay it right away, which means the first payment must be the one taken in consideration. Example:
ID | Subscription | Plan | Total | Paid | Paid at | Expires at
1 1 1 0.00 1 null 2020-11-05
2 1 1 15.00 0 null 2020-12-06
In this case the user decided to purchase the plan, but didn't pay yet. The SQL logic should read the expires_at of the row number 1.
Currently my SQL code is the following:
SELECT *
FROM payments
WHERE expires_at < '2020-11-06'
AND paid = true
and subscription_id = 1
ORDER BY id DESC
LIMIT 1
I know that this will never work because its always fetching the row number 1, no matter if I have more rows or not. I also know that I could select the last row that was paid and then verify through code if it has expired but I want to do it through SQL.
EDIT 1: SQL Fiddle to help
I created three table payments, payments_2, payments_3:
1 payments -> everything ok!
2 payments_2 -> everything ok! The user did not paid yet, therefore should grab the first row
3 payments_3 incorrect data. The user has already paid and therefore the SQL should validate the last expires_at row
CREATE TABLE `payments` (
`id` int(11) NOT NULL,
`subscription_id` int(11) NOT NULL,
`plan_id` int(11) NOT NULL,
`total` double(10,2) NOT NULL,
`expires_at` date NOT NULL,
`paid` tinyint(1) NOT NULL,
`paid_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `payments` ADD PRIMARY KEY (`id`);
INSERT INTO payments VALUES(1, 1, 1, 0, '2020-11-05', 1, null);
CREATE TABLE `payments_2` (
`id` int(11) NOT NULL,
`subscription_id` int(11) NOT NULL,
`plan_id` int(11) NOT NULL,
`total` double(10,2) NOT NULL,
`expires_at` date NOT NULL,
`paid` tinyint(1) NOT NULL,
`paid_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `payments_2` ADD PRIMARY KEY (`id`);
INSERT INTO payments_2 VALUES(1, 1, 1, 0, '2020-11-05', 1, null);
INSERT INTO payments_2 VALUES(2, 1, 1, 15.00, '2020-12-06', 1, null);
CREATE TABLE `payments_3` (
`id` int(11) NOT NULL,
`subscription_id` int(11) NOT NULL,
`plan_id` int(11) NOT NULL,
`total` double(10,2) NOT NULL,
`expires_at` date NOT NULL,
`paid` tinyint(1) NOT NULL,
`paid_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `payments_3` ADD PRIMARY KEY (`id`);
INSERT INTO payments_3 VALUES(1, 1, 1, 0, '2020-11-05', 1, null);
INSERT INTO payments_3 VALUES(2, 1, 1, 15.00, '2020-12-06', 1, '2020-11-06 15:00:00');
