0

I am developing a newspaper-like MVC webapp with an article content type which has a visits field. The database is mysql.

Each time a visitor requests an article, the visits field of the article is incremented by 1. So I can easily query article table by their overall visits.

But what I want is to sort out articles based on number of visits which each article has got in the past week.

I thought to have a separate table like weekly-stats in which I can insert and increment articles with their recent visits upon each request, but then I need to empty the table weekly as cron job, which is not ideal, and leaves the stats empty after the table is being emptied.

I also thought of having articles' visits saved in an expiring cache like redis and set the cached records to expire after a week, but then it brings all the complexities of inserting and querying redis hash elements.

So I thought there might be some more straightforward design strategies that I miss. How can I achieve this with the least hassle?

2
  • What do you exactly mean by "past week"? Is that the last complete period from Monday to Sunday (or Sunday to Saturday is you want)? Is that the last 7 full days before the current day? Is that the time period equivalent to 7 days before the current moment in time? The answer to this will have a big impact on the possible design choices that you have. Mar 4, 2018 at 11:18
  • By 'past week' I mean the last 7x24 hours.
    – Babr
    Mar 4, 2018 at 11:59

2 Answers 2

2

Have a table in which you record for each article a timestamp of when the article was visited.

For counting how often an article was visited in the last week, you can do an SQL query that counts the number of matching records, where a match is based both on the article and the recorded timestamps.

If you are worried about the storage space of this table, have a periodic job that removes all entries that are older than the longest time interval you might want reports on.

2
  • 1
    If storage is an issue one can also try binning the visits, e.g. one counter per article per hour. But this is unlikely to be necessary – given a sensible storage engine for the table, it will only grow by a few MB per month.
    – amon
    Mar 4, 2018 at 12:21
  • Using this solution will also allow OP to query for other time frames. Sorting by the most viewed article during the past 3 months could be interesting in the future and we won't have to do anything else than querying the results of this table again. Mar 4, 2018 at 16:08
0

Record visits per day and sum it over the last 7 days.

Avoid maintenance by having your insert query also delete rows older than 7 days

EDIT : table and some code

DailyVisitCount
    Date
    articleId
    count

public class VisitRepo
{
    public List<DailyVisitCount> GetVisits()
    {
        //select * from DailyVisitCount table and cache
    }

    private void UpdateCount(DateTime date, string articleId)
    {
        var sql = "Update DailyVisitCount set count = count +1 where aricleId=@articleId && date=@date;";
        var sql  += "Delete from DailyVisitCount where date < @sevendaysago;"

        //run sql
    }

    public void RecordVisit(DateTime date, string articleId)
    {
        if(GetVisits().Any(i=>i.Date == date.Date && i.ArticleId == articleId)
        {
            UpdateCount(date, articleId)
        }
        else
        {
            try
            {
                var sql = "insert into DailyVisitCount ...."
                //...
            }
            catch()
            {
                //collision with another insert, run update
            }
        }
    }
}
4
  • Do you mean after each query to insert records into the stats table I should also search the entire table and delete old rows? I guess it will impose heavy load on database if it is to happen on each article visit.
    – Babr
    Mar 4, 2018 at 12:07
  • indexes are your friend
    – Ewan
    Mar 4, 2018 at 13:24
  • remember this table will only have 7*number of articles rows
    – Ewan
    Mar 4, 2018 at 13:26
  • will you please elaborate how is the schema of the stats table that you suggest and how would you query/empty it?
    – Babr
    Mar 4, 2018 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.