lime icon

Phosphorus and Lime

A Developer's Broadsheet

This blog has been deprecated. Please visit my new blog at klenwell.com/press.
MySQL: Collaborative Filtering
Someday I'll have a use for collaborative filtering. Not today, but some day. When I do, this will be very helpful:

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


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


Have not tested it myself yet.

source: Ian B on comp.lang.php