Looking for similar items when using an intermediation table?

Asked

Viewed 40 times

2

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

Table anime:

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

Table of generos:

+----------------+-------------+
| 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 generos 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 your 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 haven’t had any difficulties, however, and if I wanted to get anime similar to anime 1 for example by sorting them based on the amount of genres they have in common, how to build this query, preferably in the same query I showed earlier, if it is not possible I am open to a separate query only to get the similar ones;

Fiddle Example

mysql select logic

  • You will have to do a subquery with Count. Post a fiddle, it already helps us mount and test "in your environment".

  • @RBZ just added the question the fiddle

  • @RBZ could also show how to return only the similar ones? Instead of returning the anime itself used as the basis for the search?

  • Posted as answer.

1 answer

2


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

The way I see it, there’s no other way subquery. So I trained and separated all animes and their genres, and the generos, I played for a subquery that brings the anime genres filtered:

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

  • It seems to work well :) is it possible to make the return not contain the anime used as the basis for the search? In other words, don’t see anime 1 but only its similar

  • 1

    Yes. Add WHERE an.id <> 1 before the GROUP BY an.id. Sqlfiddle

Browser other questions tagged mysql select logic

You are not signed in. Login or sign up in order to post.