A Pony made of (Kevin) Bacon

June 1, 2003 6:33 PM

There are so many interesting relationships here. I would enjoy a search function that queried two users and parsed them for shared threads. The output would contain all the threads that both user A and user B posted to--possibly with other criteria, such as key words? If this has been suggested before, I haven't been able to find it.
posted by squirrel to Feature Requests at 6:33 PM (14 comments total)

What a fascinating idea; personally, I have bookmarked several "user comments" pages, and scroll through them often to see where a specific user is posting. It makes a great "filter" for the 'filter as a whole, as I tend to have a lot in common with these people, and pouring through the comments on every thread just isn't possible for me as long as I continue to be cursed with employment.
posted by jonson at 6:36 PM on June 1, 2003

Seems like one of those things better left to hypothetical 'Metafilter on CD' research projects and not added as a public feature. It would not be hard to write such a query, but I think it would be kind of tough on the 'ol SQL server.
posted by crunchburger at 6:57 PM on June 1, 2003

Perfect feature for Metafilter Pro!
posted by Hildago at 7:01 PM on June 1, 2003

Who's stalking whom?
posted by Space Coyote at 7:47 PM on June 1, 2003

Sure, write me the SQL statements that would make this work and I'll look into implementing it.
posted by mathowie (staff) at 7:47 PM on June 1, 2003

something with 2 subqueries like this:

'select postid from posts, comments
where comments.postid = posts.postid
and $user1 in (select userid from comments)
and $user2 in (select userid from comments) '

is what I would try first. I havent tried that out and I kind of doubt I have it just right, but I think that approach would work. There must be some SQL gurus here, what do you think?
posted by crunchburger at 8:42 PM on June 1, 2003

Maybe you need to do the join in the subqueries too, or instead of in the main query. I'll try it tomorrow. It's the subqueries that make me think this might be a pretty costly query. But maybe there is a simpler way to do it.
posted by crunchburger at 8:44 PM on June 1, 2003

here it is as a join, rather than a subselect. (might run faster):

select p.postname
from posts p, comments c1, comments c2
where c1.postid = c2.postid
  and c1.postid=p.postid
   and c1.userid = $user1
   and c2.userid=$user2
order by p.timestamp
posted by Davidicus at 9:16 PM on June 1, 2003

Not to be snarky (who, me?) but maybe getting the regular ol' search function dialed in ('There are currently some issues with the search results, which are being worked out now') first might be the higher-priority pony to request, you know, since we're asking....
posted by stavrosthewonderchicken at 9:41 PM on June 1, 2003

stavrostheboringsensibleprioritychicken
posted by jonson at 10:32 PM on June 1, 2003

*cries*
posted by stavrosthewonderchicken at 10:39 PM on June 1, 2003

There are so many interesting relationships here.

Cabal Hunt!
posted by Shane at 5:21 AM on June 2, 2003

Collaborative filtering.
posted by MzB at 1:21 PM on June 2, 2003

not to be boring, but if you just do a regular search with the two names you want to find together, you'll get pretty good results...
posted by mdn at 7:07 PM on June 3, 2003