获得所有传递类别的MySQL多个查询/最佳解决方案

Question

创建具有正确类别的MySQL查询的最佳方法是什么？

我的想法是将一个类别数组传递给查询函数，它返回正确的内容。数组的结构将是[“艺术”、“诗歌”、“现代”、“雕塑”]。这将质疑所有的艺术/诗歌内容，只有雕塑，也有现代类别。

我已经创建了这个查询，但每次只做一个这样的工作：“艺术”或“现代”，“雕塑”--它可以得到[“艺术”、“现代”、“雕塑”]。这是为了创建一个大查询，还是我应该做多个查询，并将输出组合起来呢？

$this->db->query("SELECT
  posts.*,
  posts.id,
  JSON_ARRAYAGG(terms.slug) AS categories
FROM
  posts
  LEFT JOIN relationships ON id = objectId
  LEFT JOIN terms USING(termId)
WHERE
  status = :status
  AND termId IN (
    SELECT
      termId
    FROM
      terms
    WHERE
      slug IN (:term1, :term2)
  )
GROUP BY
  posts.id,
  objectId
HAVING
  COUNT(termId) = 2
ORDER BY
  DESC
LIMIT
  25 OFFSET 5");

$this->bind("status", "published");
$this->bind("term2", "sculpture");
$this->bind("term3", "modern");

数据库结构

// terms: 
+------------------------------------------
| termId | slug                | name      |
+------------------------------------------
| 1      | art               | Art         |
| 2      | poetry            | Poetry      |
| 3      | modern            | Modern      |
| 4      | sculpture         | Sculpture   |
| 5      | pop               | Pop         |
------------------------------------------+

// relationships: 
+-----------------------
| objectId | termId     |       
+-----------------------
| 79        | 1         |
| 71        | 5         |
| 62        | 4         |
| 59        | 4         |
| 62        | 3         |
| 91        | 4         |
| 58        | 5         |
| 54        | 4         |
| 67        | 3         |
| 54        | 3         |
-----------------------+

// posts: 
+-----------------------
| id        | ...       |       
+-----------------------
| 79        | ...       |
| 71        | ...       |
| 62        | ...       |
| 59        | ...       |
| 62        | ...       |
| 91        | ...       |
| 58        | ...       |
| 54        | ...       |
| 67        | ...       |
| 54        | ...       |
-----------------------+

Answer 1

在您的情况下，您可以尝试：

SELECT p.*,ANY_VALUE(t.slug) AS slug from posts p 
INNER JOIN relationships r ON r.objectId = p.id
INNER JOIN terms t ON t.id = r.termId
WHERE t.slug IN ('art','poetry','modern','sculpture') 
GROUP BY p.id having count(*) > 1 OR slug in ('art','poetry')