I would like to create a function like this week's popular article, which is common on blogs.
However, I am not very experienced in programming, so I am writing to ask for advice.
Currently, my image is as follows.
posts
- id
- title
view_counts
- id
- post_id
- created_at
All I can think of is to insert data one after another into view_counts and count and aggregate the number.
I think this is possible, but are there any other smart and versatile designs?
Also, is it okay to update the database for each request to count?
I would appreciate it if you could teach me how to do things wisely and think.
Thank you for your cooperation.
mysql
You can add a column called value to the view_counts table to perform the so-called Upsert.
In the case of MySQL, I think the following SQL statements can be used.
Set the post_id column to primary key (or unique key).
-- Update counter with article ID 12345
INSERT INTO view_counts
(post_id, value, created_at)
VALUES
(12345, 1, NOW(),
ON DUPLICATE KEY UPDATE
value = value+1
;
Is it okay to update the database for each request to count?
In general, it will be updated on a per-request basis.
However, if you have an order of magnitude larger PVs and do not want to access DBs on a per-request basis, we recommend using NOSQL together.
© 2024 OneMinuteCode. All rights reserved.