How to display 'InValid' records depending on, if the latest record is validated?

user_12129150 0
Viewed 51

Goal

Retrieve InValid records but with some complexity. Let me explain.

Taking a look at this output: There is an InValid record for ItemId of 1.

enter image description here

Using the query below, I am able to see InValid records - to display to the user this Item needs to be Re-Checked.

SELECT * FROM `Records` WHERE IsValid = 0;

Problem

This is where I am stuck.

In the Red is the InValid record, recorded on the 28-06-2021. The next day, 29-09-2021 the ItemId is now a Valid record.

enter image description here

But using this query below is not relevant anymore, as it will show me the records that are still Invalid. Even though the record has been validated the next day.

SELECT * FROM `Records` WHERE IsValid = 0;

My idea to solve this problem (See Edit 1 below, for further details to this solution)

My idea would be to create a Trigger that will check if today's record is valid, if it is valid, then Update all the items to true where date is before today. From here, I can use the simple query above to see InValid records.

Also, I thought of creating a History table and a trigger to see what actions have been performed on the Records table.

Question

I am not sure if my idea is appropriate to solve my problem, creating a trigger to update all previous records does not seem the records are valid at all. But, my history table will show me valid values.

Is there a query I can use and avoid creating any triggers or it's best to go with my solution?

Schema (MySQL v5.7)

-- Base
CREATE TABLE `Items` (
  Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  ItemName VARCHAR(30));

CREATE TABLE `Records` (
  Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  Date TIMESTAMP NOT NULL,
  ItemId INT NOT NULL,
  IsValid BOOLEAN NOT NULL,
  FOREIGN KEY (ItemId) REFERENCES `Items`(Id));
                                  
-- History
CREATE TABLE `Records_History` LIKE `Records`;
                                          
ALTER TABLE `Records_History`
    MODIFY COLUMN Id INT NOT NULL,
    DROP PRIMARY KEY,
    ADD Action VARCHAR(8) DEFAULT 'insert' FIRST;

CREATE TRIGGER Records_AfterInsert
    AFTER INSERT ON `Records` FOR EACH ROW
    INSERT INTO `Records_History` SELECT 'insert', d.*
    FROM `Records` AS d WHERE d.Id = NEW.Id;
                                             
-- Insert Records
INSERT INTO `Items`
    (ItemName)
VALUES
  ('Item 1'),
  ('Item 2');
                                          
 INSERT INTO `Records`
    (Date, ItemId, IsValid)
 VALUES
   ('2021-09-28', 1, 0),
   ('2021-09-28', 2, 1),
   ('2021-09-29', 1, 1),
   ('2021-09-29', 2, 1);

Query #1

select * from `Records`;
Id Date ItemId IsValid
1 2021-09-28 00:00:00 1 0
2 2021-09-28 00:00:00 2 1
3 2021-09-29 00:00:00 1 1
4 2021-09-29 00:00:00 2 1

Query #2

select * from `Records_History`;
Action Id Date ItemId IsValid
insert 1 2021-09-28 00:00:00 1 0
insert 2 2021-09-28 00:00:00 2 1
insert 3 2021-09-29 00:00:00 1 1
insert 4 2021-09-29 00:00:00 2 1

View on DB Fiddle

Edit 1

Unfortunately my solution is not an option. As I will hit this error:

Tried my solution: Error Code: 1442. Can't update table 'Records' in stored function/trigger because it is already used by statement which invoked this stored function

This basically means: I have a chance of causing an infinite loop.

mysql-5.7 mysql sql
1 Answers

This is what I have done to achieve my goal:

Schema (MySQL v5.7)

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

CREATE TABLE `Items` (
  Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  ItemName VARCHAR(30));

INSERT INTO `Items`
    (ItemName)
VALUES
  ('Item 1'),
  ('Item 2');

CREATE TABLE `Records` (
  Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  Date TIMESTAMP NOT NULL,
  ItemId INT NOT NULL,
  IsValid BOOLEAN NOT NULL,
  FOREIGN KEY (ItemId) REFERENCES `Items`(Id));
  
 INSERT INTO `Records`
    (Date, ItemId, IsValid)
 VALUES
   ('2021-09-28', 1, 1),
   ('2021-09-28', 2, 1),
   ('2021-09-29', 1, 0),
   ('2021-09-29', 2, 1),
   ('2021-09-30', 1, 1),
   ('2021-09-30', 2, 1),
   ('2021-10-01', 1, 0),
   ('2021-10-01', 2, 1);

Query #1

SELECT * FROM Records a WHERE IsValid = 0 AND ItemId NOT IN (
       SELECT ItemId FROM Records b WHERE IsValid = 1 AND b.Date >= a.Date
    ) GROUP BY ItemId;
Id Date ItemId IsValid
7 2021-10-01 00:00:00 1 0

View on DB Fiddle

user_121291500
Related