Working with tags in Mysql

Question

In a system I would like to categorize some users with tags. However there are groups of tags (for example, UF tag group, Interests). I’m trying to do a query where I can answer some questions like:

I want all users in the state of SP or MG and who like Football and Basketball.

My table structure is as follows:

Table users: id, name

Table tags: id, name

Table user_tag: id, user_id, tag_id

I tried with the following query:

SELECT users.*, tags.nome AS nome_tag FROM users 
    INNER JOIN user_tag ON user_tag.user_id = users.id 
    INNER JOIN tags ON tags.id = user_tag.tag_id 
WHERE 
    (tags.nome = 'SP' OR tags.nome = 'MG') AND 
    (tags.nome = 'Futebol' AND tags.nome = 'Basquete')

I did not succeed with the query above. In the example above would be used tags of the UF group and the Sport group but could have other groups.

What is the best way to work with this approach?

Thank you.

Answer 1

Try it like this:

select * from users where 
    id in ( 
      select user_tag.user_id from tags JOIN user_tag ON user_tag.tag_id = tags.id
      where tags.nome IN ('MG', 'SP') 
    )

    AND id IN (        
      select user_tag.user_id from tags JOIN user_tag ut ON user_tag.tag_id = tags.id
      where tags.nome IN ('basquete', 'futebol') 
    );

in this model you search for the tags you need and return the related users, for me it is simpler to understand, because the user must be related to the 2 groups of tags.

Answer 2

EDIT: Correcting to not get an invalid answer, follows new select, is now very close to what was already posted here, but still is a select "different" just one more option to do the same thing, let the select dry!.

Making use of information from entree of Mr. Leandroluk, but with a different solution in SELECT goes below:

SELECT distinct u.*,t1.nome,t2.nome FROM 
users u, tags t1, tags t2, user_tag r1, user_tag r2
WHERE
u.id = r1.user_id and
u.id = r2.user_id and
t1.id = r1.tag_id and 
t2.id = r2.tag_id and   
t1.nome in ('SP','MG') and (t2.nome in ('Futebol','Basquete'))

building the context

drop database if exists test;
CREATE DATABASE test;
USE test;

CREATE TABLE `test`.`users` (
  `id` INT NOT NULL AUTO_INCREMENT  COMMENT '',
  `nome` VARCHAR(45) NULL COMMENT '',
  PRIMARY KEY (`id`)  COMMENT '');

CREATE TABLE `test`.`tags` (
  `id` INT NOT NULL AUTO_INCREMENT  COMMENT '',
  `nome` VARCHAR(45) NULL COMMENT '',
  PRIMARY KEY (`id`)  COMMENT '');

CREATE TABLE `test`.`user_tag` (
  `id` INT NOT NULL AUTO_INCREMENT  COMMENT '',
  `user_id` INT NOT NULL COMMENT '',
  `tag_id` INT NOT NULL COMMENT '',
  PRIMARY KEY (`id`)  COMMENT '');

-- REFERENCIANDO DAS FOREIN KEY
ALTER TABLE `test`.`user_tag` 
ADD INDEX `fk_tag_idx` (`tag_id` ASC)  COMMENT '',
ADD INDEX `fk_user_idx` (`user_id` ASC)  COMMENT '';
ALTER TABLE `test`.`user_tag` 
ADD CONSTRAINT `fk_tag`
  FOREIGN KEY (`tag_id`)
  REFERENCES `test`.`tags` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_user`
  FOREIGN KEY (`user_id`)
  REFERENCES `test`.`users` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;


-- populando users
INSERT INTO users VALUES (1,'Joao');
INSERT INTO users VALUES (2,'Pedro');
INSERT INTO users VALUES (3,'Maria');
INSERT INTO users VALUES (4,'Ana');
-- populando tags
INSERT INTO tags VALUES (1,'SP');
INSERT INTO tags VALUES (2,'MG');
INSERT INTO tags VALUES (3,'Basquete');
INSERT INTO tags VALUES (4,'Futebol');
-- populando user_tag
INSERT INTO user_tag(user_id,tag_id) VALUES (1,1); -- > Joao  | SP
INSERT INTO user_tag(user_id,tag_id) VALUES (2,1); -- > Pedro | SP
INSERT INTO user_tag(user_id,tag_id) VALUES (3,1); -- > Maria | SP
INSERT INTO user_tag(user_id,tag_id) VALUES (4,2); -- > Ana   | MG
INSERT INTO user_tag(user_id,tag_id) VALUES (1,3); -- > Joao  | Basquete
INSERT INTO user_tag(user_id,tag_id) VALUES (1,4); -- > Joao  | Futebol
INSERT INTO user_tag(user_id,tag_id) VALUES (2,1); -- > Pedro | Basquete
INSERT INTO user_tag(user_id,tag_id) VALUES (3,2); -- > Maria | Futebol
INSERT INTO user_tag(user_id,tag_id) VALUES (4,3); -- > Ana   | Basquete
INSERT INTO user_tag(user_id,tag_id) VALUES (4,4); -- > Ana   | Futebol

outworking

+----+------+------+----------+
| id | nome | nome | nome     |
+----+------+------+----------+
|  1 | Joao | SP   | Basquete |
|  1 | Joao | SP   | Futebol  |
|  4 | Ana  | MG   | Basquete |
|  4 | Ana  | MG   | Futebol  |
+----+------+------+----------+
4 rows in set (0.01 sec)

Answer 3

First of all I believe that the way you are working is not functional because logically you do not use a reference as the user state in a tag. For better treatment use a new column so it makes it easier both in queries and views.

Your solution is below (I tested it on Sqlserver, so the language is just like his):

--construindo o contexto
CREATE DATABASE teste
GO
USE teste
GO
CREATE TABLE users    ( [id] INT, [nome] VARCHAR(100) )
GO
CREATE TABLE tags     ( [id] INT, [nome] VARCHAR(100) )
GO
CREATE TABLE user_tag ( [user_id] INT, [tag_id] INT )
GO
--populando users
INSERT INTO users VALUES (1,'Joao');
INSERT INTO users VALUES (2,'Pedro');
INSERT INTO users VALUES (3,'Maria');
INSERT INTO users VALUES (4,'Ana');
--populando tags
INSERT INTO tags VALUES (1,'SP');
INSERT INTO tags VALUES (2,'MG');
INSERT INTO tags VALUES (3,'Basquete');
INSERT INTO tags VALUES (4,'Futebol');
--populando user_tag
INSERT INTO user_tag(user_id,tag_id) VALUES (1,1); --> Joao  | SP
INSERT INTO user_tag(user_id,tag_id) VALUES (2,1); --> Pedro | SP
INSERT INTO user_tag(user_id,tag_id) VALUES (3,1); --> Maria | SP
INSERT INTO user_tag(user_id,tag_id) VALUES (4,2); --> Ana   | MG
INSERT INTO user_tag(user_id,tag_id) VALUES (1,3); --> Joao  | Basquete
INSERT INTO user_tag(user_id,tag_id) VALUES (1,4); --> Joao  | Futebol
INSERT INTO user_tag(user_id,tag_id) VALUES (2,1); --> Pedro | Basquete
INSERT INTO user_tag(user_id,tag_id) VALUES (3,2); --> Maria | Futebol
INSERT INTO user_tag(user_id,tag_id) VALUES (4,3); --> Ana   | Basquete
INSERT INTO user_tag(user_id,tag_id) VALUES (4,4); --> Ana   | Futebol
--solucao
SELECT 
    *
FROM 
    users
WHERE
    --verifica se é ou de SP ou de MG
    (   SELECT TOP 1 COUNT(*)
        FROM user_tag
        INNER JOIN tags ON (user_tag.tag_id = tags.id)
        WHERE 
            user_tag.user_id = users.id AND
            tags.nome IN ('SP','MG')
    )  = 1 AND
    --verifica se gosta de Basquete
    (   SELECT TOP 1 COUNT(*)
        FROM user_tag
        INNER JOIN tags ON (user_tag.tag_id = tags.id)
        WHERE
            user_tag.user_id = users.id AND
            tags.nome = 'Basquete'
    ) = 1 AND
    --verifica se gosta de Futebol
    (   SELECT TOP 1 COUNT(*) 
        FROM user_tag 
        INNER JOIN tags ON (user_tag.tag_id = tags.id) 
        WHERE
            user_tag.user_id = users.id AND
            tags.nome = 'Futebol'
    )  = 1

Basically what you should do is evaluate each query tag in a WHERE. When using the TOP 1 in the sub-query you guarantee that the result will be a single entry for the comparison in the original query. If you had more comparisons to make, you would just need to add one more sub-volume.