Phosphorus and Lime
A Developer's Broadsheet
This blog has been deprecated. Please visit my new blog at klenwell.com/press.
profile
Name: klenwell
Home: San Diego, California, United States
About Me: designer, programmer, wordsmith
Wednesday, October 26, 2005
Someday I'll have a use for collaborative filtering. Not today, but some day. When I do, this will be very helpful:
So what this means:
First, the idea is that you're trying to find the "this other item" in phrase "People who liked this item also liked this other item," a la Amazon.
What the different variables mean:
Have not tested it myself yet.
source: Ian B on comp.lang.php
SELECT t2.col_item,Count(t2.col_item) AS popularity FROM table AS t1
LEFT JOIN table AS t2 ON t1.col_arbiter = t2.col_arbiter
WHERE t1.col_item = 'this_item'
AND t2.col_item <> 'this_item'
GROUP BY t2.col_item
ORDER BY Count(t2.col_item) DESC
LIMIT 5
LEFT JOIN table AS t2 ON t1.col_arbiter = t2.col_arbiter
WHERE t1.col_item = 'this_item'
AND t2.col_item <> 'this_item'
GROUP BY t2.col_item
ORDER BY Count(t2.col_item) DESC
LIMIT 5
So what this means:
First, the idea is that you're trying to find the "this other item" in phrase "People who liked this item also liked this other item," a la Amazon.
What the different variables mean:
table - MySQL table containing items and their arbiters
t1 - temporary table containing every item in table
t2 - temp table containing every association between items (common denom: col_arbiter) in table
col_item - items that are being associated
t1.col_item - in temp table 1, column holding items such as "this item" in phrase above
t2.col_item - in temp table 2, column holding "this other item" in phrase above
col_arbiter - (from L. arbiter elegantiarum), column for people whose taste is being queried
t1 - temporary table containing every item in table
t2 - temp table containing every association between items (common denom: col_arbiter) in table
col_item - items that are being associated
t1.col_item - in temp table 1, column holding items such as "this item" in phrase above
t2.col_item - in temp table 2, column holding "this other item" in phrase above
col_arbiter - (from L. arbiter elegantiarum), column for people whose taste is being queried
Have not tested it myself yet.
source: Ian B on comp.lang.php
some rights reserved, © 2006