Who read this also read that algorithm

Question

0.00/5 (No votes)

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 30-Dec-13 0:07am

AndyInUK

Add a Solution

2 solutions

Solution 3

You may take reference from my example below:

First, you need 3 tables in your mysql database with minimum fields as follows:
1. 'reader' table with 'readerid' and 'readername' ('readid' is primary key)
2. 'article' table with 'articleid' and 'articlename' ('articleid' is primary key)
3. 'reader_article' table with 'readerid' and 'articleid' (both composite 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
(SELECT  ra2.readerid  FROM reader_article ra2 WHERE ra2.articleid="11")
GROUP BY ra1.aricleid ORDER BY COUNT(ra1.articleid) DESC LIMIT 3

Posted 30-Dec-13 2:16am

Peter Leow

Updated 30-Dec-13 2:17am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andrius Leonavicius · Accepted Answer · 2013-12-30T02:14:00

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
       INNER JOIN UserAndArticle AS t2
               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.

I hope this was helpful.

Posted 30-Dec-13 2:14am

Andrius Leonavicius

Comments

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.

Andrius Leonavicius 14-Feb-14 19:15pm

Hi,

I just (accidentally) noticed that I haven't replied to this. Sorry about that. You probably figured it out, but I'm going to tell you anyway.

You could eliminate viewed articles by adding the following code in a WHERE statement:

AND NOT EXISTS (SELECT 1
FROM UserAndArticle AS t3
WHERE t3.ArticleID = t2.ArticleID
AND t3.UserID = @UserID)