在PostgreSQL中按字母顺序搜索朋友

Question

应该如何查询来选择朋友，如：

用户表

+--------+--------------+-------------+
|   id   |  first_name  |  last_name  |
+--------+--------------+-------------+
| 1      | John         | Doe         |
| 2      | Andrew       | Quin        |
| 3      | Sara         | Cambel      |
| 4      | Samanda      | Mint        |
| 5      | Bill         | Smith       |
+--------+--------------+-------------+

朋友桌

+--------+---------------+---------------+
|   id   |  user_one_id  |  user_two_id  |
+--------+---------------+---------------+
| 1      | 1             | 2             |
| 2      | 1             | 5             |
| 3      | 1             | 4             |
| 4      | 1             | 3             |
+--------+---------------+---------------+

当我们在有id 1的用户朋友中搜索"s“时，结果应该是：

| 4      | Samanda      | Mint        |
| 3      | Sara         | Cambel      |
| 5      | Bill         | Smith       |

“萨曼达造币厂”排在第一位，因为"s“字排在第一位，”萨拉·坎贝尔“和”萨姆“字母顺序排在”萨尔“前面。

"Bill Smith“是第三位的，因为它包含"s”字，但"s“的位置比以前的名字晚。

"Andrew“没有出现在结果中，因为名字中没有"s”字符。

当前查询

SELECT u.id, u.first_name, u.last_name 
FROM friends f
LEFT JOIN users u ON u.id=f.user_two_id
WHERE f.user_one_id=1 AND LOWER(u.first_name || ' ' || u.last_name) LIKE :query;

Answer 1

使用辅助列full_name

select u.id, first_name, last_name
from users u
join friends f on user_two_id = u.id,
lower(concat(first_name, last_name)) full_name
where user_one_id = 1
and full_name like '%s%'
order by position('s' in full_name), full_name;

 id | first_name | last_name 
----+------------+-----------
  4 | Samanda    | Mint
  3 | Sara       | Cambel
  5 | Bill       | Smith
(3 rows)