0

I want to implement a Viewed system for my website. Here is the structure of my tables:

// table1
id | user_or_ip | post_id | date_time   // inserting new row for each viewed

// table2
id | post_id | total_viewed             // getting the number of total viewed for each post

Now I need to a trigger for insert/update table2 after insert in table1.

I think I have to use on duplicate key.

stack
  • 10,280
  • 19
  • 65
  • 117

1 Answers1

1

You can do this fairly easily.

With the following 2 example table:-

CREATE TABLE table1
(
    id  INT NOT NULL AUTO_INCREMENT,
    user_or_ip  VARCHAR(255),
    post_id INT,
    date_time   DATETIME,
    PRIMARY KEY (id)
);

CREATE TABLE table2
(
    id  INT NOT NULL AUTO_INCREMENT,
    post_id INT,
    total_viewed    INT,
    PRIMARY KEY (id),
    UNIQUE KEY post_id (post_id)
);

you can use the following trigger on table 1 to calculate the count and insert it to table 2:-

CREATE TRIGGER `trig_1` AFTER INSERT ON `table1`
 FOR EACH ROW BEGIN
INSERT INTO table2(post_id, total_viewed) 
SELECT post_id, COUNT(*)
FROM table1
WHERE post_id = NEW.post_id
GROUP BY post_id
ON DUPLICATE KEY UPDATE total_viewed = VALUES(total_viewed);
END

Note that if you are certain that there will never be an error you could just insert a count of 1 and set it to total_count + 1 in the ON DUPLICATE KEY clause. But if anything fails that prevents the trigger the counts will be forever wrong for that post_id:-

CREATE TRIGGER `trig_1` AFTER INSERT ON `table1`
 FOR EACH ROW BEGIN
INSERT INTO table2(post_id, total_viewed) 
VALUES(NEW.post_id, 1)
ON DUPLICATE KEY UPDATE total_viewed = total_viewed + 1;
END

Note also that the sub query to get the count will be more efficient with an index on post_id in table1

Kickstart
  • 21,403
  • 2
  • 21
  • 33
  • Thank, +1. But I have 2 questions: 1. why did you use `group by`? (user_or_ip, post_id) are unique. 2. why should it calculates the total viewed after each new viewed? (as you said), please show me how can it just ++ total viewed after each new viewed ... – stack Nov 19 '15 at 15:10
  • I have used GROUP BY as I am returning the post_id in the SELECT and I prefer to keep to SQL standards with regards to having all non aggregate fields listed in the GROUP BY clause. – Kickstart Nov 19 '15 at 15:19
  • Ok, Just one thing. I want to create a unique-group for (`user_or_ip`, `post_id`) in table1. actually I want to prevent of increasing the number of total viewed several times by one person. what I want to do is correct? – stack Nov 19 '15 at 15:23
  • With the first trigger think you could just use _COUNT(DISTINCT user_or_ip, post_id)_ instead of a _COUNT(*)_ . With the 2nd trigger I think you would need some way of checking whether a combination had already been recorded, which is probably not worthwhile. – Kickstart Nov 19 '15 at 16:17
  • But the way, still I believe, your first query does not need to `GROUP BY post_id`. Because you used of `WHERE post_id = NEW.post_id`. Am I wrong? – stack Nov 20 '15 at 07:35
  • 1
    In MySQL it will depend on the ini settings. In standard SQL it would probably error without the GROUP BY – Kickstart Nov 20 '15 at 09:08
  • Ah, I got it now. Thanks. *(sorry for asking again, but it is my last question)*: I want to use of your second trigger, now, I want to know how can I prevent of increasing the number of total viewed for each post by one user more that 1 time? I have to create a unique-group on (`user_or_ip`, `post_id`). So is there any better solution? *(because you told "which is probably not worthwhile")* – stack Nov 21 '15 at 11:35
  • You would need to have a table listing users who have already been counted for a post. So that means an extra insert, or storing the user ids in a field on the counts table (very bad idea). Both would be slower and more complicated than the first solution. – Kickstart Nov 23 '15 at 08:50