用于检索友谊的MYSQL查询只能用于一种方式吗？

I have a query that should be retrieving a user's friends.

我有一个应该检索用户的朋友的查询。

Given the situation that we are user two and we sent the request to user one the query should return the account information for user one. (SQLfiddle of this scenario) [Works correctly]

鉴于我们是用户2的情况并且我们将请求发送给用户1,查询应该返回用户1的帐户信息。 (本场景的SQLfiddle)[正确工作]

Given the situation that we are user one and we recieved the request from user two the query should return the account information for user two. (SQLfiddle of this scenario) [Returning empty result]

鉴于我们是用户1并且我们收到来自用户2的请求的情况,该查询应该返回用户2的帐户信息。 (本场景的SQLfiddle)[返回空结果]

So the query works fine as long as we are the user that sent the request, however if we are the user that received the request it doesn't work.

因此,只要我们是发送请求的用户,查询就可以正常工作,但是如果我们是收到请求的用户,则它不起作用。

I can fix this by switching the JOINs, LEFT => RIGHT, RIGHT => LEFT. But that breaks the first scenario.
I can fix both my doing a union of the two queries. (SQLfiddle of this scenario) but surely that's not the most efficent way to do this. So, Basic question: how should I create this query?

我可以通过切换JOIN来解决这个问题,LEFT => RIGHT,RIGHT => LEFT。但这打破了第一种情况。我可以修复我做两个查询的联合。 (这个场景的SQLfiddle)但肯定不是最有效的方法。所以,基本问题:我该如何创建这个查询?

SCENARIO ONE:

SELECT `accounts`.*,
  IFNULL(`f1`.`sender`,        `f2`.`sender`)        AS `sender`,
  IFNULL(`f1`.`recipient`,     `f2`.`recipient`)     AS `recipient`,
  IFNULL(`f1`.`date_sent`,     `f2`.`date_sent`)     AS `date_sent`,
  IFNULL(`f1`.`date_reviewed`, `f2`.`date_reviewed`) AS `date_reviewed`,
  IFNULL(`f1`.`accepted`,      `f2`.`accepted`)      AS `accepted` 
FROM `accounts` 
  LEFT  JOIN `friendships` AS `f1` ON `accounts`.`id` = `f1`.`sender` 
  RIGHT JOIN `friendships` AS `f2` ON `accounts`.`id` = `f2`.`recipient` 
WHERE (`f1`.`recipient` = 2 OR `f2`.`sender` = 2);

PATCHED SCENARIO TWO

已修补的情景二

SELECT `accounts`.*,
  IFNULL(`f1`.`sender`,        `f2`.`sender`)        AS `sender`,
  IFNULL(`f1`.`recipient`,     `f2`.`recipient`)     AS `recipient`,
  IFNULL(`f1`.`date_sent`,     `f2`.`date_sent`)     AS `date_sent`,
  IFNULL(`f1`.`date_reviewed`, `f2`.`date_reviewed`) AS `date_reviewed`,
  IFNULL(`f1`.`accepted`,      `f2`.`accepted`)      AS `accepted` 
FROM `accounts` 
  RIGHT JOIN `friendships` AS `f1` ON `accounts`.`id` = `f1`.`sender` 
  LEFT  JOIN `friendships` AS `f2` ON `accounts`.`id` = `f2`.`recipient` 
WHERE (`f1`.`recipient` = 2 OR `f2`.`sender` = 2);

WORKING FOR BOTH SCENARIOS (Question is basically; how can I do this without a union?):

为两个角色工作(基本上是问题;如果没有工会,我怎么能这样做?):

SELECT `accounts`.*,
  IFNULL(`f1`.`sender`,        `f2`.`sender`)        AS `sender`,
  IFNULL(`f1`.`recipient`,     `f2`.`recipient`)     AS `recipient`,
  IFNULL(`f1`.`date_sent`,     `f2`.`date_sent`)     AS `date_sent`,
  IFNULL(`f1`.`date_reviewed`, `f2`.`date_reviewed`) AS `date_reviewed`,
  IFNULL(`f1`.`accepted`,      `f2`.`accepted`)      AS `accepted` 
FROM `accounts` 
  LEFT  JOIN `friendships` AS `f1` ON `accounts`.`id` = `f1`.`sender` 
  RIGHT JOIN `friendships` AS `f2` ON `accounts`.`id` = `f2`.`recipient` 
WHERE (`f1`.`recipient` = 1 OR `f2`.`sender` = 1)
UNION
SELECT `accounts`.*,
  IFNULL(`f1`.`sender`,        `f2`.`sender`)        AS `sender`,
  IFNULL(`f1`.`recipient`,     `f2`.`recipient`)     AS `recipient`,
  IFNULL(`f1`.`date_sent`,     `f2`.`date_sent`)     AS `date_sent`,
  IFNULL(`f1`.`date_reviewed`, `f2`.`date_reviewed`) AS `date_reviewed`,
  IFNULL(`f1`.`accepted`,      `f2`.`accepted`)      AS `accepted` 
FROM `accounts` 
  RIGHT  JOIN `friendships` AS `f1` ON `accounts`.`id` = `f1`.`sender` 
  LEFT JOIN `friendships` AS `f2` ON `accounts`.`id` = `f2`.`recipient` 
WHERE (`f1`.`recipient` = 1 OR `f2`.`sender` = 1);

SCHEMA CREATION:

CREATE TABLE `accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `status` tinyint(4) DEFAULT '0',
  `type` varchar(25) NOT NULL,
  `username` varchar(25) NOT NULL,
  `first_name` varchar(80) NOT NULL,
  `last_name` varchar(80) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

/*Data for the table `accounts` */

insert  into `accounts`
(`id`,`status`,`type`,`username`,`first_name`,`last_name`) 
values 
(1,1,'writer','user1','User','One'),
(2,1,'writer','user2','User','Two'),
(3,1,'publisher','user3','User','Three');

CREATE TABLE `friendships` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sender` int(11) NOT NULL,
  `recipient` int(11) NOT NULL,
  `date_sent` datetime DEFAULT NULL,
  `date_reviewed` datetime DEFAULT NULL,
  `accepted` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

/*Data for the table `friendships` */

insert  into `friendships`
(`id`,`sender`,`recipient`,`date_sent`,`date_reviewed`,`accepted`) 
values (3,2,1,'2013-07-16 20:54:46',NULL,NULL);

1 个解决方案

#1

Please try this query. I removed two joins. And only one join is needed, both side will work.

请尝试此查询。我删除了两个连接。并且只需要一个连接,双方都可以工作。

SELECT `accounts`.*, `f1`.`sender`  AS `sender`,`f1`.`recipient`  AS `recipient`,`f1`.`date_sent`  AS `date_sent`,  `f1`.`date_reviewed` AS `date_reviewed`,`f1`.`accepted`    AS `accepted` FROM `accounts`   JOIN `friendships` AS `f1` ON (`accounts`.`id` = `f1`.`sender`   or `accounts`.`id` = `f1`.`recipient`) WHERE    if(`f1`.`recipient` = 1, `f1`.`sender` , if(`f1`.`sender` = 1, `f1`.`recipient` , 0)) = `accounts`.`id`;