mysql - 根据作用于第二个表的 where 子句，通过数据透视表过滤链接到另一个表的表行

Question

+--------------+
|  paintings   |
+--------------+
| id | title   |
+----+---------+
| 1  | muzelf1 |
| 2  | muzelf2 |
| 3  | muzelf3 |
+----+---------+

+----------------------------------------+
|                  tags                  |
+----------------------------------------+
| id | type            | name            |
+----+-----------------+-----------------+
| 1  | painting_medium | oil_painting    |
| 2  | painting_style  | impressionistic |
| 3  | painting_medium | mixed_media     |
| 4  | painting_medium | watercolours    |
| 5  | painting_style  | mixed_media     |
| 6  | painting_style  | photorealistic  |
+----+-----------------+-----------------+


+---------------------------+
|       paintings_tags      |
+---------------------------+
| id | painting_id | tag_id |
+----+-------------+--------+
| 1  | 1           | 1      |
| 2  | 1           | 2      |
| 3  | 2           | 4      |
| 4  | 3           | 2      |
| 5  | 3           | 1      |
+----+-------------+--------+

sql

CREATE TABLE paintings (
id integer AUTO_INCREMENT PRIMARY KEY,
title text
);

INSERT INTO paintings(id,title) VALUES
 (1,'muzelf1'),(2,'muzelf2'),(3,'muzelf3');

CREATE TABLE tags (
id integer AUTO_INCREMENT PRIMARY KEY,
name text,
type text
);

INSERT INTO tags(id,name,type) VALUES
(1,'oil_painting','painting_medium')
,(2,'impressionistic','painting_style')
,(3,'mixed_media','painting_medium')
,(4,'watercolours','painting_medium')
,(5,'mixed_media','painting_style')
,(6,'photorealistic','painting_style');

CREATE TABLE paintings_tags (
id integer AUTO_INCREMENT PRIMARY KEY,
painting_id integer,
tag_id integer
);

INSERT INTO paintings_tags(id,painting_id,tag_id) VALUES
(1,1,1)
,(2,1,2)
,(3,2,4)
,(4,3,2)
,(5,3,1);

Find all the paintings with [{tags.type="painiting_medium", tags.name="oil_painitng"},{tags.type="painiting_style", tags.name="impressionistic"}].

+-----------------------------------+
|         Expected Output           |
+-----------------------------------+
| id | painting_title | painting_id |
+----+----------------+-------------+
| 1  | muzelf1        | 1           |
+----+----------------+-------------+
| 2  | muzelf3        | 3           |
+----+----------------+-------------+

这是我尝试使用bookShelf ORM和knex查询构建器做的事情。

 Painting.query(function (qb) {
    qb.innerJoin('painting_tags','paintings.id','painting_tags.painting_id')
            .innerJoin('tags','painting_tags.tag_id','tags.id')
            .where(qb => {
              tagFilters.forEach(filter => {
                qb.where('tags.type',filter.type).andWhere('tags.name',filter.name)
              })
            });
    });

仅当标记过滤器数组只有一个元素时，上述方法才有效。但我需要它适用于数组中的所有过滤器。

What would a raw query look like for the above? And how can I convert the same to work using ORM and query builder?

Answer 1

这是一个想法:

SELECT p.id painting_id
     , p.title
     , MAX(CASE WHEN t.type = 'painting_medium' THEN t.name END) medium
     , MAX(CASE WHEN t.type = 'painting_style' THEN t.name END) style 
  FROM paintings p 
  JOIN paintings_tags pt 
    ON pt.painting_id = p.id 
  JOIN tags t 
    ON t.id = pt.tag_id 
 GROUP 
    BY p.id;
+-------------+---------+--------------+-----------------+
| painting_id | title   | medium       | style           |
+-------------+---------+--------------+-----------------+
|           1 | muzelf1 | oil_painting | impressionistic |
|           2 | muzelf2 | watercolours | NULL            |
|           3 | muzelf3 | oil_painting | impressionistic |
+-------------+---------+--------------+-----------------+

您可以将其作为子查询(或使用 HAVING)进行过滤，但是，除非数据集很大，否则我倾向于使用一些 JavaScript 进行过滤。