通过优先考虑用户关系来搜索社交网络的最佳方法是什么? [英] what's the best way to search a social network by prioritizing a users relationships first?

问题描述

我建立了一个社交网络，我想通过一个api搜索条目.社交网络的数据库是mysql.我希望搜索以以下格式返回结果:与查询匹配并且是执行搜索的用户朋友的结果应优先于与查询简单匹配的结果.

I have a social network set up and via an api I want to search the entries. The database of the social network is mysql. I want the search to return results in the following format: Results that match the query AND are friends of the user performing the search should be prioritized over results that simply match the query.

那么这可以在一个查询中完成吗?还是我必须做两个单独的查询并合并结果并删除重复项?

So can this be done in one query or will I have to do two separate queries and merge the results and remove duplicates?

我可能可以使用Lucene构建数据结构并有效地搜索该索引，但是我想知道每次创建新关系时更新文档的代价是否会过高?

I could possibly build up a data structure using Lucene and search that index efficiently, but am wondering if the penalty of updating a document everytime a new relationship is created is going to be too much?

谢谢

推荐答案

对Lucene的引用使该方程式有些复杂.让我们先解决它(至少要获得一个基线).

The reference to Lucene complicates the equation a little bit. Let's solve it (or at least get a baseline) without it first.

假设以下数据模型(或即将来临的数据模型.

Assuming the following datamodel (or something approaching.

tblUsers
  UserId  PK
  UserName
  Age
  ...

tblBuddies
  UserId     FK to tblUsers.UserId
  FriendId   tblUsers.Userid  = Id of one of the friends
  BuddyRating     float 0.0 to 1.0 (or whatever normalized scale) indicating 
                  the level of friendship/similarity/whatever

tblItems
  ItemId  PK
  ItemName
  Description
  Price
  ...

tblUsersToItems
   UserId   FK to tblUsers.UserId
   ItemId   FK to 
   ItemRating   float 0.0 to 1.0 (or whatever normalized scale) indicating 
                the "value" assigned to item by user.

天真的查询(但为优化查询奠定了良好的基础)可能是:

A naive query (but a good basis for an optimized one) could be:

SELECT [TOP 25]  I.ItemId, ItemName, Description, SUM(ItemRating * BuddyRating)
FROM tblItems I
LEFT JOIN tblUserToItems UI ON I.ItemId = UI.ItemId
LEFT JOIN tblBuddies B ON UI.UserId = B.FriendId
WHERE B.UserId = 'IdOfCurrentUser'
  AND SomeSearchCriteria -- Say ItemName = 'MP3 Player'
GROUP BY I.ItemId, ItemName, Description
ORDER BY SUM(ItemRating * BuddyRating) DESC

想法是，如果给定的项目是由朋友推荐/使用的，则它的重量会更大.如果朋友是密友[BuddyRating]和/或如果朋友更强烈地推荐此商品[ItemRating]

The idea is that a given item is given more weight if it is recommended/used by a friend. The extra weigh is the more important if the friend is a a close friend [BuddyRating] and/or if the friend recommend this item more strongly [ItemRating]

优化这样的查询取决于项目的总数，给定用户拥有的好友的平均/最大数量，用户在其列表中可能拥有的项目的平均/最大数量.

Optimizing such a query depends on the overal number of item, the average/max numbers of buddies a given user has, the average/max number of items a user may have in his/her list.

您正在寻找这种类型的想法/信息吗?还是我想念这个问题?

Is this type of ideas/info you are seeking or am I missing the question?

这篇关于通过优先考虑用户关系来搜索社交网络的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！