MySQL multiple queries / best solution to get all passed categories-CodePudding

What would be the best way to create a MySQL query that would get the right posts with the correct categories.

My idea is that I pass an array of categories to the query function and it returns the correct content. The structure of the array would be ["art", "poetry", ["modern", "sculpture"]]. This would query all the art/poetry content and only sculptures that also have the modern category.

I have created this query but this only works for one at a time like: ["art"] or ["modern", "sculpture"] it can get ["art", ["modern", sculptures"]]. Is this even vise to try to create one big query or should I do multiple queries and just combine the output?

$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");

Database structure

// 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        | ...       |
-----------------------

CodePudding user response：

In your case you can try:

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')