2

I managed to have a query for getting all the friends and friends of friends of a particular user. I have a users table and a friendships join table. For simplicity, lets just say that the users table only has a primary key. The friendships table has the user_id and friend_id columns. The query is as follows:

"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 is the id of the user whom I wish to get all the friends and friends of friends.

This query works very well however I would like to convert this to a query usable by sphinx's sql_attr_multi. I tried directly placing it like so:

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

but that obviously failed.

How do I make this query usable by sphinx's indexer?

Thanks in advance for anything that will point me in the right direction.

Lester Celestial
  • 1,454
  • 1
  • 16
  • 26

1 Answers1

2

I don't know enough about your data structure to say that this will 100% work, but I see one obvious problem:

WHEREusers.id=fof.friend_idANDfriends.friend_id=fof.user_idANDfriends.user_id= $id

The last part, WHERE user_id = $id is wrong. That syntax works when adding a sql_query_info attribute to a sphinx index, but not to sql_attr_multi.

Your query for the sql_attr_multi should select the id as the first column that matches the document id in your main sql_query.

The second column of the sql_attr_multi is the col you want to group together in the attr.

Jon
  • 3,280
  • 2
  • 15
  • 16
  • Thanks for the tip, however I am still unsure as to how to go about this. This means that I need to create a different query? – Lester Celestial Nov 23 '10 at 21:13
  • 1
    No. Your main query to grab the data for the index (sql_query) should be selecting an id. This id needs to match the first column selected in your sql_attr_multi query. The second col you select in your sql_attr_multi query is the one you want to actually search for values. You don't use WHERE id = $id in sql_attr_multi (selecting that id column achieves the same thing) – Jon Nov 23 '10 at 21:22
  • After hours of trying to figure this out I am still at a road block. I have seen other queries that uses joins? As I am no expert at sql queries, is it possible for my query to have a "joins" version? – Lester Celestial Nov 24 '10 at 09:47
  • 1
    Yes, you can use joins. The requirement is the two columns you select in your query--the rest can be anything else you need to get those columns. – Jon Nov 26 '10 at 22:09