MySQL Trigger – Insert Row if Count is Greater or Equal

countMySQLtrigger

I do 99% php/javascript work so getting my head around SQL is always a struggle, sorry if I seem dense.

So I've got a table that looks something like this: 

post_id   |  meta_key    | meta_value
--------------------------------------
1         | requirement  | 3
2         | requirement  | 1
3         | requirement  | 1
1         | adder_id     | 3
1         | adder_id     | 2
1         | adder_id     | 69
2         | adder_id     | 42
2         | complete_date| 20190309040445

On an insert trigger I want to run a query that will for each post_id that dose not have a "complete date":

INSERT INTO this_table ( post_id, meta_key, meta_value )
VALUES
( var_post_id, 'complete_date',NOW());

But only if

SELECT COUNT(*)
FROM this_table
WHERE post_id = var_post_id 
AND meta_key = 'adder_id'

is >= 

SELECT meta_value
FROM this_table
WHERE post_id = var_post_id
AND meta_key = 'requirement'

So post_id two is the intended effect (requires 1, has 1, got a complete date) while post_id one is an example of something that should trigger an update (requires 3, has 3, waiting on a complete date) and post_id three should be ignored (requires 1, has 0, just ignore).

Now this would be easy enough with an if/else and 2 or three variables but is this even possible with a mySQL statement? Is there an easier way of going about it within mySQL or should I try and find a PHP solution instead?

Thanks for any assistance, I don't usually ask for help here unless I'm properly stumped and this one really has me going.

Best Answer

Start with getting all the required counts:

SELECT
  post_id,
  meta_value AS required_count
FROM
  this_table
WHERE
  meta_key = 'requirement'

Then derive another row set, which is the actual counts per post_id:

SELECT
  post_id,
  COUNT(*) AS actual_count
FROM
  this_table
WHERE
  meta_key = 'adder_id'
GROUP BY
  post_id

Now join the two sets as derived tables and filter the results on actual_count >= required_count to get the post_ids fulfilling the requirement:

SELECT
  req.post_id
FROM
  (
    SELECT
      post_id,
      meta_value AS required_count
    FROM
      this_table
    WHERE
      meta_key = 'requirement'
  ) AS req

  INNER JOIN
  (
    SELECT
      post_id,
      COUNT(*) AS actual_count
    FROM
      this_table
    WHERE
      meta_key = 'adder_id'
    GROUP BY
      post_id
  ) AS act

  ON req.post_id = act.post_id

WHERE
  act.actual_count >= req.required_count

You can simplify the last query by joining the act set directly to this_table and slightly modifying the WHERE condition by referencing meta_value instead of required_count:

SELECT
  req.post_id
FROM
  this_table AS req
  INNER JOIN
  (
    SELECT
      post_id,
      COUNT(*) AS actual_count
    FROM
      this_table
    WHERE
      meta_key = 'adder_id'
    GROUP BY
      post_id
  ) AS act  ON req.post_id = act.post_id
WHERE
  -- the filter from the first derived table is moved here
  req.meta_key = 'requirement'

  -- the 'req.required_count' reference is no longer valid or needed;
  -- use 'req.meta_value' instead
  AND act.actual_count >= req.meta_value

We are almost there. Now you need to filter out the post_ids that already have a completed_date. This is called an anti-join (a self-anti-join in this specific case, as you excluding rows from a table based on a condition involving the same table). There are different methods of implementing an anti-join. Last I checked, the method that worked in MySQL better than other methods was the one involving a left join and an IS NULL check. This is how it would be implemented in your case:

SELECT
  req.post_id
FROM
  this_table AS req
  INNER JOIN
  (
    SELECT
      post_id,
      COUNT(*) AS actual_count
    FROM
      this_table
    WHERE
      meta_key = 'adder_id'
    GROUP BY
      post_id
  ) AS act  ON req.post_id = act.post_id

  -- part of anti-join implementation
  LEFT JOIN this_table AS exc ON req.post_id = exc.post_id
                             AND exc.meta_key = 'completed_date'

WHERE
  req.meta_key = 'requirement'
  AND act.actual_count >= req.meta_value

  -- part of anti-join implementation
  AND exc.post_id IS NULL

An outer join could be basically described as an attempt to join. So by using a LEFT JOIN you are attempting to join each req row (which is all the requirement rows) to each exc row (all the completed_date rows) on post_id. The IS NULL check in the WHERE clause is saying that actually you want the rows where there was no match.

Now that you have all the right post_id values, it only remains to add 'complete_date', NOW() to the SELECT list, and you are ready to insert new rows into the table:

INSERT INTO
  this_table (post_id, meta_key, meta_value)
SELECT
  req.post_id, 'complete_date', NOW()
FROM
  this_table AS req
  INNER JOIN
  (
    SELECT
      post_id,
      COUNT(*) AS actual_count
    FROM
      this_table
    WHERE
      meta_key = 'adder_id'
    GROUP BY
      post_id
  ) AS act  ON req.post_id = act.post_id
  LEFT JOIN this_table AS exc ON req.post_id = exc.post_id
                             AND exc.meta_key = 'completed_date'
WHERE
  req.meta_key = 'requirement'
  AND act.actual_count >= req.meta_value
  AND exc.post_id IS NULL
;
Related Question