代码之家 › 专栏 › 技术社区 › Lester Celestial

Sphinx indexer针对sql\u attr\u multi的自定义查询

thinking-sphinx sphinx mysql ruby-on-rails

Lester Celestial · 技术社区 · 15 年前

"SELECT DISTINCT friends.user_id, friends.friend_id, users.*

  FROM FRIENDSHIPS friends, USERS 

  WHERE USERS.id = friends.friend_id AND friends.user_id = #{u.id}

  UNION

  SELECT DISTINCT fof.user_id, fof.friend_id, users.*

  FROM FRIENDSHIPS friends, FRIENDSHIPS fof, USERS

  WHERE USERS.id = fof.friend_id AND friends.friend_id = fof.user_id AND friends.user_id = #{u.id}"

u、 id是我希望得到所有朋友和朋友的朋友的用户id。

这个查询工作得很好,但是我想把它转换成sphinx的sql_attr_multi可用的查询。我试着直接这样写:

sql_attr_multi = uint fwfof from query; \
  SELECT DISTINCT `friends`.`user_id`, `friends`.`friend_id`, `users`.`id` \
  FROM `friendships` friends, `users` \
  WHERE `users`.`id` = `friends`.`friend_id` AND `friends`.`user_id` = $id \
  UNION \
  SELECT DISTINCT `fof`.`user_id`, `fof`.`friend_id`, `users`.`id` \
  FROM `friendships` `friends`, `friendships` `fof`, `users` \
  WHERE `users`.`id` = `fof`.`friend_id` AND `friends`.`friend_id` = `fof`.`user_id` AND `friends`.`user_id` = $id

但这显然失败了。

如何让sphinx的索引器使用这个查询?

提前感谢你为我指明方向的一切。