分析表以了解用户之间的相互喜欢 - Analyze table for mutual likes between users

Analyze table for mutual likes between users

本文关键字：喜欢之间用户了解 | 更新日期: 2023-09-27

在我的数据库中，我有一个表（喜欢），列出了用户喜欢的项目.
在访问用户个人资料时，我需要确定我们有多少共同的"喜欢".
编写显示用户之间相互喜欢的查询的最佳方法是什么？

likes table
 id  |  user  |  item  | activated
-----------------------------------
 1   |  3     |  14    | 1          
 2   |  4     |  14    | 1

在这个例子中，我需要返回 14。

假设应该是这样的：

select userViewer.item, items.itemName
from likes userViewer,
     likes userProfile,
     items
where userProfile.user = $profileUserId
  and userViewer.user = $userViewer
  and userProfile.item = userViewer.item
  and items.item = userViewer.item

其中$profileUserId - 配置文件用户的用户 ID

和 $userViewer - 当前用户的用户 ID

与"

项目"表的"连接"表单上的相同查询，例如：

select userViewer.item, items.itemName
from likes userViewer inner join likes userProfile 
on userProfile.user = $profileUserId
  and userViewer.user = $userViewer
  and userProfile.item = userViewer.item
inner join items 
on userViewer.item = items.item

我想知道为什么你没有在 where 子句中使用匹配的 caondition

SELECT 
    userViewer.item, items.itemName
  FROM 
     likes AS userViewer 
  INNER JOIN 
    likes as userProfile 
  ON 
    userProfile.item = userViewer.item
  INNER JOIN items 
  ON
    userViewer.item = items.item
  WHERE
    userViewer.user = $userViewer
    AND
    userProfile.user = $profileUserId