php - MySQL - 根据关系表的值在最终输出中添加额外的列

Question

自从我使用 MySQL 以来已经有一段时间了，我无法自己解决这个问题:我有三张表，一张用于 users，一张用于 books，一张用于收藏夹。我正在开发一个搜索系统，用户可以在其中根据书名、出版年份或类型搜索书籍。

books 和 users 表都没有什么特别之处，所以我认为发布这两个表的模式是无关紧要的。favorites 表是一个关系表，由两个 外键 列组成，book_id 和 user_id。

我的目标是运行一个查询，该查询根据搜索查询获取所有书籍，但也会查找 favorites 表，如果用户已将该特定书籍添加到他/她的收藏夹中，则添加最终输出的额外列，f.e isFavorable: 0，否则:isFavorable: 1。最终输出看起来像这样(JSON格式):

{
  data: [
    {
      // non favorite
      "id": 1,
      "title": "Test Book 1",
      "genre": "Adventure",
      "year": 1994,
      "isFavorite": 0 // if cannot be found in the favourites table
    },

    // favorite
    {
      "id": 2,
      "title": "Test Book 2",
      "genre": "Novel"
      "year": 2012,
      "isFavorite": 1 // if can be found in the favorites table
    }
  ]
}

到目前为止，我只能输出所有标记为收藏的结果，而不是所有的书，并且无法弄清楚如何检查它是否已经被标记为收藏并输出相应的值。

这将打印用户 #1 喜欢的所有书籍

SELECT * FROM books b 
LEFT JOIN favourites f ON f.book_id = b.id 
LEFT JOIN users u ON u.id = f.user_id 
WHERE u.id = 1

Answer 1

我会尝试:

SELECT
 b.id
 , b.title
 , b.year
 , b.genre
 , IF(f.book_id IS NOT NULL, 1, 0) AS isFavorite 
FROM books b 
LEFT JOIN favourites f ON f.book_id = b.id AND f.user_id = 1
WHERE
 ... e.g. b.year = 3099 AND b.genre = 'Sport'

1. 删除 users 表，因为您只需要 favorites 中的 user_id
2. 将条件从 WHERE 子句移到 ON 子句中，因为您还想拥有(还)不喜欢的书
3. 使用 IF 语句来确定您的用户是否已经喜欢这本书