15,938,218 members
See more:
Hello All,

Am trying to work out on best possible algorithm for "who read this also read that". Basically there is a table with all the data i.e. user id and article id.

1. Say I go to article ID 11.
2. Now the system needs to find out all the users who viewed article 11.
3. Now take those user ids and find out the other articles they viewed and populate the best 3 articles for "who read this also read that"

Step 2 is easy. But there might be 1000s of users who viewed article 11. So say now system got the information 2222 users viewed article 11.

Now the confusing part is how to calculate step 3??

Any suggestions??
Posted

## Solution 3

You may take reference from my example below:

First, you need 3 tables in your mysql database with minimum fields as follows:
2. 'article' table with 'articleid' and 'articlename' ('articleid' is primary key)

The answer to step 3 is like this:
SQL
```SELECT a.articlename, COUNT(ra1.articleid) FROM reader_article ra1 INNER JOIN article a ON ra1.articleid = a.articleid WHERE ra1.readerid IN
GROUP BY ra1.aricleid ORDER BY COUNT(ra1.articleid) DESC LIMIT 3```

v2

## Solution 2

Hi,

I can give you example how to do something like this in SQL Server (should be very similar in MySQL too).

First, let’s say that you have table named UserAndArticle with the following data:
XML
```ID	UserID	ArticleID
1	10	101
2	10	102
3	10	103
4	20	101
5	20	103
6	30	101
7	30	103
8	40	101
9	40	102
10	40	103
11	50	102
12	50	103
13	60	101
14	60	102
15	70	101
16	70	102
17	70	104
18	70	105
19	70	106```

SQL Server script would be something like this (in MySQL you need to change TOP with LIMIT clause):
SQL
```DECLARE @ArticleID INT = 102

SELECT TOP 3 t2.ArticleID,
COUNT(t2.ArticleID) AS NoOfViews
FROM   UserAndArticle AS t1
ON t1.UserID = t2.UserID
WHERE  t1.ArticleID = @ArticleID
AND t2.ArticleID <> @ArticleID
GROUP  BY t2.ArticleID
ORDER  BY NoOfViews DESC```

In this example, I am searching the best 3 articles by the number of views who read article 102.
Result:
XML
```ArticleID	NoOfViews
101	4
103	3
105	1```

Also, you may want to eliminate current user ArticleIDs from the result.

AndyInUK 2-Jan-14 5:40am
Hello Andrius,

Thanks for your reply. I am just trying to understand your solution. So basically what you did here is -
1. Searched the users who viewed article 102
2. Then scanned all the users viewed article and on the basis of maximum views the best 3 result is displayed and the same article id should not be displayed.

If this is how this query works then great.. i wasn't expecting such a simple solution to the problem. And also how can I remove current user ArticleIDs from the result?

Thanks
Andrius Leonavicius 2-Jan-14 11:03am
Hi,

It basically works as you described. You can remove current user ArticleIDs by adding this line:
AND t2.UserID <> @UserID
AndyInUK 2-Jan-14 6:14am
I guess just adding this line should remove the current user -
AND t2.UserID <> '@UserID'
Andrius Leonavicius 2-Jan-14 11:04am
That's right (parameter should not be in quotes).
AndyInUK 8-Jan-14 11:20am
I think there is a small problem here. by adding AND t2.UserID <> '@UserID', it will remove that particular UserID from the list but it will not removed all the articles that particular user viewed.

It's suggesting the same articles again and again and just remove the article it's coming from.