How to show a list of recommendations based on friends of friends

I'm trying to do a mysql query to display a list of recommendations. The list of recommendations sorted by proximity of friends. I was able to sort the list based on the recommendations of friends, but I am having trouble sorting a list of recommendations based on friends of friends.

The following is the design of the tables:

/*Table structure for table `recommendation` */

CREATE TABLE `recommendation` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,  
  `kategori` varchar(255) NOT NULL,  
  `nama` varchar(255) NOT NULL,  
  `rating` set('1','2','3','4','5') NOT NULL,  
  `writer_id` varchar(255) NOT NULL,  
  `search_here` longtext NOT NULL,  
  `user_type` set('0','1') NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`),  
  KEY `kategori` (`kategori`),
  KEY `nama` (`nama`),

  FULLTEXT KEY `search_here` (`search_here`)
)
ENGINE=MyISAM AUTO_INCREMENT=100174
DEFAULT CHARSET=latin1 COMMENT='recommendation table';


/*Table structure for table `user_relationship` */

DROP TABLE IF EXISTS `user_relationship`;
CREATE TABLE `user_relationship` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `me` varchar(255) NOT NULL,
  `friend` varchar(255) NOT NULL,

  PRIMARY KEY (`id`),
  KEY `me` (`me`)
)
ENGINE=MyISAM AUTO_INCREMENT=100982
DEFAULT CHARSET=latin1 COMMENT='user relationship table';

I apply the following query:

SELECT
  a.id AS ids,
  a.writer_id,
  a.kategori,
  a.rating,
  b.me,
  a.user_type
FROM
  recommendation a
LEFT OUTER JOIN
  user_relationship b
    ON
      a.writer_id = b.friend
    AND
      b.me='$USER_ID'
WHERE
  MATCH(search_here) AGAINST('$SEARCH' IN BOOLEAN MODE)
ORDER BY
  b.me DESC,
  a.writer_id

The 开发者_如何学Clist of recommendations should shown in the following order: recommendations from friends, recommendations from friends of friends, and recommendations from the public.

anyone can help me?

thanks

EDIT:

I have tried create clone of user_relationship table named "user_friend_friend". the "user_friend_friend" table design like this:

CREATE TABLE `user_friend_friend` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `friend` varchar(255) NOT NULL,
  `friend2` varchar(255) NOT NULL,

  PRIMARY KEY (`id`),
  KEY `friend` (`friend`)
)
ENGINE=MyISAM AUTO_INCREMENT=100982
DEFAULT CHARSET=latin1 COMMENT='user friend friend table';

I do following query:

SELECT 
a.id AS ids,
a.writer_id,
a.kategori,
a.nama,
a.rating,
b.me, b.friend,
c.friend2 AS ff, a.user_type 
FROM recommendation a
LEFT OUTER JOIN webref.user_relationship b 
ON a.oleh = b.friend AND b.me='1010147270'
LEFT OUTER JOIN webref.user_friend_friend c 
ON a.oleh = c.friend AND b.friend IS NULL

WHERE MATCH(search_here) AGAINST('$SEARCH' IN BOOLEAN MODE)
GROUP BY a.id
ORDER BY 
    b.me DESC,
    user_type DESC, 
    rating DESC,
    c.friend DESC,
    a.writer_id

is there any more efficient solution?

A few performance tips: - You should have an index on columns you are joining. For example it doesn't look like writer_id has an index. - Using VARCHAR(256) as the type for a column you are joining is slow even if you use an index. It would be better to store the ids as integers. You could have a separate table that you stores the int -> varchar mapping that you query later.

But to do the friend of friend query, something like this should work:

SELECT
  a.id AS ids,
  a.writer_id,
  a.kategori,
  a.rating,
  b.me,
  a.user_type
FROM
  recommendation a
LEFT OUTER JOIN
  user_relationship b
    ON
      a.writer_id = b.friend
    AND
      b.me='$USER_ID'
LEFT OUTER JOIN
  user_relationship c
    ON
      a.writer_id = c.friend
    AND
      c.me = b.friend