MySQL UNION、Order By 和 IN 语句合一查询

Question

好的，我正在为 MySQL 开发一个搜索功能，但遇到了一些我似乎无法解决的问题。我正在尝试制作一个脚本来搜索用户 friend ，并以更好的分数返回 friend 。这是我目前所拥有的:

SELECT id, first_name, last_name, image_id FROM users
WHERE id IN (
    SELECT receiver AS friend FROM friends WHERE initiator = :me AND status = :status        
     UNION
    SELECT initiator AS friend FROM friends WHERE receiver = :me AND status = :status
) ORDER BY score AND concat(first_name, ' ', last_name) LIKE '%test query%';

现在一切正常，直到我尝试按分数对 friend 进行排序。任何人都可以锻炼如何按分数对 friend 进行排序吗？如果你需要我澄清任何事情，尽管问。

我有以下两个表。

用户表:

friend 表:

这里要求的是一些示例数据:

例如，我有三个用户:

ID: 1, first_name: Michael, last_name: Sample
ID: 2, first_name: John, last_name: Citizen
ID: 3, first_name: Caitlin, last_name: Wired

下面的第一个条目将通过显示 Michael 发起好友请求并且 Caitlin(接收者)接受它来使 Michael 和 Caitlin 成为 friend 。 (因此状态为 1)他们的基本分数是 10。第二个条目显示 John 也是凯特琳的 friend (由凯特琳发起)但得分更高。

ID: 10000, initiator: 1, receiver: 3, status: 1, score: 10
ID: 10001, initiator: 3, receiver: 2, status: 1, score: 20

所以在这个样本数据的情况下。如果凯特琳是搜索她 friend 的用户，它应该显示约翰而不是迈克尔。

Answer 1

外部语句仅从 users 列中进行选择，该列没有 score 列。几乎任何使用子选择的语句都可以(并且应该)使用连接重写。在这种特殊情况下，它将允许在子句中使用所有 friends 字段。

SELECT u.id, u.first_name, u.last_name, u.image_id 
  FROM users AS u
    JOIN friends AS f 
      ON ...

子选择的 WHERE 子句中使用的表达式成为连接条件。联合在某种意义上等同于 OR(两者基本上都是 conjunctions )。

      ON    (u.id = f.receiver AND f.initiator=:me AND status=:status)
         OR (u.id = f.initiator AND f.receiver=:me AND status=:status)

声明的其余部分几乎保持原样。

  WHERE concat(first_name, ' ', last_name) LIKE '%test query%'
  ORDER BY score
;

总的来说，就是:

SELECT u.id, u.first_name, u.last_name, u.image_id 
  FROM users AS u
    JOIN friends AS f 
      ON    (u.id = f.receiver AND f.initiator=:me AND status=:status)
         OR (u.id = f.initiator AND f.receiver=:me AND status=:status)
  WHERE concat(first_name, ' ', last_name) LIKE '%test query%'
  ORDER BY score
;

请注意，这是未经测试的，就在我的脑海中。