Where in a select on a system using a many relationship type for many

Asked

Viewed 99 times

1

I’m making a basic system that has a table of projects, one of tags and a pivot table, project_tag. Each project has a id, title and a tag. The table of tags has a id and a title . The table projeto_tag has a projeto_id and a tag.id, logo, each project has a table for each tag, thus making a relationship from many to many. Follow the tables below:

Project table:

CREATE TABLE projects (
    user_id    INTEGER,
    id         INTEGER  NOT NULL
                        PRIMARY KEY AUTOINCREMENT,
    title      TEXT,
    create_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (
        user_id
    )
    REFERENCES users (id) ON DELETE CASCADE
                          ON UPDATE CASCADE
);

Tag table:

CREATE TABLE tags (
    id  INTEGER       NOT NULL
                      PRIMARY KEY AUTOINCREMENT,
    tag VARCHAR (255) 
);

Table project_tag:

CREATE TABLE project_tag (
    id         INTEGER NOT NULL
                       PRIMARY KEY AUTOINCREMENT,
    project_id INTEGER,
    tag_id     INTEGER,
    FOREIGN KEY (
        project_id
    )
    REFERENCES projects (id) ON DELETE CASCADE
                             ON UPDATE CASCADE,
    FOREIGN KEY (
        tag_id
    )
    REFERENCES tags (id) ON DELETE CASCADE
                         ON UPDATE CASCADE
);

I want to filter projects based on the tags a user has passed, example:

['React.js', 'Knex', e por ai vai...]

,But, well, at least the way I’m selecting it, it’s returning one for each tag that it has, not all of the joints, which makes the Where process difficult, I think.

inserir a descrição da imagem aqui

That said, I can’t make a where that satisfies my will, which is to filter the projects based on the tags that I passed, for example: if I pass the tag "React.js", return all the projects that have this tag and also the others. I did some research, but I couldn’t find.

Observing: I am using the Knex in the back-end and my database is the Sqlite3. Code in SQL that is making the select and generating the return of the fourth image:

SELECT projects.id, tags.tag, tags.id FROM projects 
INNER JOIN project_tag on project_tag.project_id = projects.id 
INNER JOIN tags on project_tag.tag_id = tags.id
sql sqlite3 knex.js

  • No database was informed, but already tried the clause WHERE tags.tag IN("React.js", "Knex")

  • Already. The problem is that since it is returning several columns, one for each tag, although the project did not have the "Knex" tag, it would return the project with the "React" tag. Example: a project with the tags "React.js" and "Angular"

  • Detail your data model by asking the SQL of the tables involved in the operation, the name of the database (Important to define the SQL syntax to be used) and if possible a sample of the data so we can replicate your problem.

  • I’ve given the question a redo to try to make it a little clearer, thank you.

  • Where are the CREATE TABLE to replicate your problem?

  • I refactored again, buddy

  • 1

    @Junior3874, the way you put it, we would have to finger-type everything inside the images. With selectable text, we can just copy and paste in a test environment to reproduce your problem and try to help.

  • I changed it already, buddy

Show 3 more comments

1 answer

3


Make a filter using the clauses:

  • WHERE nome_da_coluna IN(valor+):
    Where the clause WHERE determines which lines should be returned according to a criterion, operator IN allows you to specify a set of values to test membership.
    nome_da_coluna is the column containing the values to be used by the filtration.
    valor+ are one or more values used as a filter parameter
  • GROUP BY nome_da_coluna HAVING COUNT(*) = quantidade_de_valores:
    The clause GROUP BY specifies that the results should be grouped and the clause HAVING specifies that only groups that meet the specified conditions should be returned.
    nome_da_coluna is the column determining how the grouping. quantidade_de_valores determines the repetition count within the group to determine whether the group has passed.

Examples:

Select all projects that have both tags "React.js", "Knex":

SELECT projects.id AS 'Project ID', tags.tag, tags.id  AS 'Tag ID' FROM projects 
INNER JOIN project_tag on project_tag.project_id = projects.id 
INNER JOIN tags on project_tag.tag_id = tags.id 
WHERE tags.tag IN("React.js", "Knex")
GROUP BY projects.id HAVING COUNT(*) = 2

Select all projects that have at least one of the tags "React.js", "Knex":

SELECT projects.id AS 'Project ID', tags.tag, tags.id  AS 'Tag ID' FROM projects 
INNER JOIN project_tag on project_tag.project_id = projects.id 
INNER JOIN tags on project_tag.tag_id = tags.id 
WHERE tags.tag IN("React.js", "Knex")
GROUP BY projects.id HAVING COUNT(*) = 1

Select all projects that do not have tags "React.js", "Knex":

SELECT projects.id AS 'Project ID', tags.tag, tags.id  AS 'Tag ID' FROM projects 
INNER JOIN project_tag on project_tag.project_id = projects.id 
INNER JOIN tags on project_tag.tag_id = tags.id 
WHERE tags.tag IN("React.js", "Knex")
GROUP BY projects.id HAVING COUNT(*) = 0
  • 1

    Perfect, man. Perfectly matched what I wanted.

Browser other questions tagged sql sqlite3 knex.js

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