[Tagdb] SQL Queries for "relatedness" of tags

[Nitin Borwankar]

Joshua Lippiner wrote:

> I just read the relatedness article on Tagschema about users, ideas 
> and tags.  While interesting, I am still failing to see the SQL 
> translation of this analysis.
>  
> I have two tables:
>  
> USERS (user_id, username)
> TAGS (tag_id, user_id, tag)
>  
> What I need to do is find all users with similar tags to the user I 
> care about.  So assume that I am looking at User A's tags and I want 
> to query the database to find all users with tags LIKE User A.  What I 
> clearly need to do (simplistically speaking) is find all users with 
> all User A's tags, then all user's with All-1, All-2, All-3, etc until 
> I reached the max number of users that I want.  Technically, I would 
> also want to "rank" each tag with some weight so tags that are used 
> less often that User A has in common with others is worth more then 
> common tags, but I want to leave that out of it for now.
>  
> Ideally I would like to be able to create a SQL query that can be run 
> as called by the user instead of running some form of a batch process. 
>

Hi Joshua,


I'll  break this down in steps so the logic is clear.  I always find it 
useful to do this - I am not one of those people from whose brain 
queries spring fully formed and perfectly optimized.

a)  all tags for user I care about

select tag_id from tags where user_id = <id_of_user_I_care_about>

this gives us a "set of tags of interest"

b) All users with a certain set of tags

select user_id from tags where tag_id in ( "set of tags of interest ") 

c) Now we combine the two

select user_id from tags where tag_id in ( select tag_id from tags where 
user_id = <id_of_user_I_care_about> )

now if you want username you can get that from the users table from user_id.

Hope this helps,


Nitin Borwankar
http://tagschema.com











> How are others tackling this problem?  It also applies to anything 
> else that might be tagged other then users (e.g. items, whatever).
>  
> Thank you for your suggestions.
>  
>  
> Josh Lippiner
>  
> IdeaDisco.com - Express yourself (c)/  Coming Soon/
>
>------------------------------------------------------------------------
>
>_______________________________________________
>Tagdb mailing list
>Tagdb at lists.tagschema.com
>http://lists.tagschema.com/mailman/listinfo/tagdb
>  
>