php - 根据 friend 的帖子选择特定标题

Question

我有一些 table 。

标题

id| title

1 | Cars
2 | Computers
3 | Phones
4 | Tvs

条目

id | title_id | user_id | entry    | time
1  | 1        | 12      | entry-01 | 1
2  | 2        | 11      | entry-02 | 2
3  | 3        | 12      | entry-03 | 3
4  | 2        | 11      | entry-04 | 4
5  | 3        | 11      | entry-05 | 5
6  | 4        | 12      | entry-06 | 6
7  | 4        | 13      | entry-07 | 7
8  | 4        | 11      | entry-08 | 8
9  | 1        | 10      | entry-09 | 9
10 | 2        | 12      | entry-10 | 10

用户

id | username 
10 | user-1
11 | user-2
12 | user-3
13 | user-4

friend

id | user_id | friend_id 
1  | 10      | 12
2  | 11      | 12
3  | 12      | 10
4  | 10      | 11

我需要根据 friend 的条目过滤标题，并按 (entry.time) desc 对结果进行排序。我还需要在列表中显示 friend 的姓名和计数(条目)。

按user_id=10过滤的预期结果是:

结果

1 | Computers | user-3, user-2(2)
2 | Tvs       | user-2, user-3
3 | Phones    | user-2, user-3
4 | Cars      | user-3

有什么想法吗？

Answer 1

这个问题很复杂，但是如果您养成将问题分解为更小部分的习惯，您很快就会明白。为什么不首先获取用户 ID 10 的所有好友呢？我们可以这样做:

SELECT CASE WHEN user_id = 10 THEN friend_id
        WHEN friend_id = 10 THEN user_id END AS userFriends
FROM friends
GROUP BY userFriends
HAVING userFriends IS NOT NULL;

请注意 case 语句的使用，因为 user_id 10 可能位于两列中的任意一列中。我使用 GROUP BY 以防用户/ friend 对出现多次(例如您的示例中的 10 和 12)，并检查不为空以删除与大小写不匹配的行。

现在您已经有了这些，您可以将其与条目和标题表连接起来以获得您需要的信息。只需添加一些聚合即可获取每个用户的标题条目数:

SELECT t.title, u.userName, COUNT(*) AS numEntries
FROM titles t
LEFT JOIN entry e ON e.title_id = t.id
JOIN users u ON u.id = e.user_id
JOIN(
  SELECT 
    CASE WHEN user_id = 10 THEN friend_id
    WHEN friend_id = 10 THEN user_id END AS userFriends
  FROM friends
  GROUP BY userFriends
  HAVING userFriends IS NOT NULL) f ON f.userFriends = u.id
GROUP BY t.title, u.userName;

匹配您的格式将非常棘手。通常，您可以使用 GROUP_CONCAT() 获取逗号分隔的列表，但您将在第一个列表中获得类似 user3, user2, user2 的内容。为了解决这个问题，我建议在 select 语句中编写 CONCAT() 来修改上述查询，以获取每个用户一侧的条目数。此外，使用另一个 CASE 语句，以便仅当 COUNT(*) 大于 1 时才会发生这种情况:

SELECT t.title, 
  CASE WHEN COUNT(*) > 1 THEN
    CONCAT(u.userName, ' (', COUNT(*), ')')
  ELSE
    u.userName
  END AS numEntries
FROM titles t
LEFT JOIN entry e ON e.title_id = t.id
JOIN users u ON u.id = e.user_id
JOIN(
  SELECT 
    CASE WHEN user_id = 10 THEN friend_id
    WHEN friend_id = 10 THEN user_id END AS userFriends
  FROM friends
  GROUP BY userFriends
  HAVING userFriends IS NOT NULL) f ON f.userFriends = u.id
GROUP BY t.title, u.userName;

现在，我将对该查询执行 GROUP_CONCAT():

SELECT tmp.title, GROUP_CONCAT(tmp.userEntries) AS friendEntries
FROM(
  SELECT t.title, 
    CASE WHEN COUNT(*) > 1 THEN
      CONCAT(u.userName, ' (', COUNT(*), ')')
    ELSE
      u.userName
    END AS userEntries
  FROM titles t
  LEFT JOIN entry e ON e.title_id = t.id
  JOIN users u ON u.id = e.user_id
  JOIN(
    SELECT 
      CASE WHEN user_id = 10 THEN friend_id
      WHEN friend_id = 10 THEN user_id END AS userFriends
    FROM friends
    GROUP BY userFriends
    HAVING userFriends IS NOT NULL) f ON f.userFriends = u.id
  GROUP BY t.title, u.userName) tmp
GROUP BY tmp.title;

对于冗长的回复，我深表歉意(尽管我想澄清并涵盖所有内容)。如果您已经做到了这一点，您会很高兴知道它在 SQL Fiddle 中有效。 .