Search for similar items when using a brokerage table?

Question

Search for similar items when using a brokerage table?

Navigation

#1 by (2 votes)

2

Following some advice to optimize queries in MySQL, I decided to split a list of items into 3 tables:

Table animes:

+----------------+-------------+
| id             | titulo      |
+----------------+-------------+
| 1              | Anime A     |
+----------------+-------------+
| 2              | Anime B     |
+----------------+-------------+
| 3              | Anime C     |
+----------------+-------------+

Gender table:

+----------------+-------------+
| id             | genero      |
+----------------+-------------+
| 1              | Ação        |
+----------------+-------------+
| 2              | Romance     |
+----------------+-------------+
| 3              | Ninja       |
+----------------+-------------+

And then a third table used to make the link between the anime table and the gen table, defining which genre each anime belongs to.

+----------------+-------------+
| anime_id       | genero_id   |
+----------------+-------------+
| 1              | 1           |
+----------------+-------------+
| 1              | 3           |
+----------------+-------------+
| 2              | 2           |
+----------------+-------------+
| 2              | 3           |
+----------------+-------------+
| 3              | 2           |
+----------------+-------------+

Given the ID of an anime, I can get its categories using the following query:

SELECT an.id, an.titulo, GROUP_CONCAT(g.genero) AS generos
    FROM animes an 

    INNER JOIN tabela_intermediacao ti ON ti.anime_id = an.id

    INNER JOIN generos g ON g.id = ti.genero_id

WHERE an.id = 1

So far, I have not had any difficulties, but what if I wanted to get animes similar to anime 1 for example, ordering them based on how many genres they have in common, how to construct this query, preferably in the same query that I showed before, if it is not possible I'm open to a separate query just to get the similar ones;

Fiddle Example

mysql select

asked by anonymous 17.07.2018 / 02:32

1 answer

I can not get list of an API SQL Parameter in PDO PHP does not work

score 2 · Accepted Answer

It works this way, but as soon as I can see others.

As I see it, you can not do anything other than subquery . So I filtered and separated all animes and their genres, and generos , filtered by a subquery that brings the genres of filtered anime:

SELECT an.id, an.titulo, GROUP_CONCAT(g.genero) AS generos
FROM animes an
INNER JOIN anime_genero ti ON ti.anime_id = an.id
INNER JOIN generos g ON g.id = ti.genero_id
GROUP BY an.id
HAVING generos = (
SELECT GROUP_CONCAT(g.genero) AS generos
FROM animes an
INNER JOIN anime_genero ti ON ti.anime_id = an.id
INNER JOIN generos g ON g.id = ti.genero_id
WHERE an.id = 1
)

Example in SQLFiddle