[Tagdb] SQL Queries for "relatedness" of tags

[Nitin Borwankar]

Nitin Borwankar wrote:

> 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
>
>

there's a more opaque way of doing this which is using a self-join on 
the tags table


select a.user_id from  tags  a, tags b, where  a.tag_id = b.tag_id  and  
b.user_id =  <user_id_of_user_I_care_about>

( going from left to right in this SQL,  get all the users whose tags 
match those of the user I care about )


This is more compact but much harder to explain,  especially if the 
concept of self-join is unfamiliar.


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
>>  
>>
>
> _______________________________________________
> Tagdb mailing list
> Tagdb at lists.tagschema.com
> http://lists.tagschema.com/mailman/listinfo/tagdb