Postgres- delete record not existing in array as value referenced in another table-CodePudding

I am trying to write an SQL query with PostgreSQL where I want to DELETE team_member_tag table record no longer presented in the array of $tags. The parameter is found in the tag table.

tag table:

id	slug
3742	first-tag
3743	second-tag

team_member_tag table:

id	team_member_id	tag_id
89263	68893	3742
89264	68893	3743

The catch is that I should be referenced by the ID value and it can be found by the slug value.

My example:

    $tags = ['first-tag'];

    foreach ($tags as $tag) {
    
            $stmt = $this->getConnection()->prepare('
                DELETE FROM team_member_tag tmt
                WHERE t.slug = :tag NOT IN
                (SELECT t.id FROM tag t)
             ');

            $stmt->bindValue('tag', $tag);

            $stmt->executeQuery();
        }

foreach ($tags as $tag) {
    
            $stmt = $this->getConnection()->prepare('
                 DELETE FROM team_member_tag tmt
                 WHERE NOT EXISTS(SELECT t.id
                 FROM tag t
                 WHERE t.slug = :tag)
             ');

            $stmt->bindValue('tag', $tag);

            $stmt->executeQuery();
        }

In this example, the second-tag reference should be deleted from the team_member_tag table.

My SQL examples are not working so I need help to find it by slug value in the tag table and delete it in team_member_tag if it does not exist in the array of values.

CodePudding user response：

you need to get all tag_ig fromthe tag table, that must be deleted

$tags = ['first-tag'];

foreach ($tags as $tag) {

        $stmt = $this->getConnection()->prepare('
            DELETE FROM team_member_tag tmt
            WHERE tmt.tag_id NOT IN
            (SELECT t.id FROM tag t WHERE t.slug = :tag )
         ');

        $stmt->bindValue('tag', $tag);

        $stmt->executeQuery();
    }

Here you can see that it works

CREATE TABLE tag (
  "id" INTEGER,
  "slug" VARCHAR(10)
);

INSERT INTO tag
  ("id", "slug")
VALUES
  ('3742', 'first-tag'),
  ('3743', 'second-tag');

CREATE TABLE team_member_tag (
  "id" INTEGER,
  "team_member_id" INTEGER,
  "tag_id" INTEGER
);

INSERT INTO team_member_tag
  ("id", "team_member_id", "tag_id")
VALUES
  ('89263', '68893', '3742'),
  ('89264', '68893', '3743');

CREATE TABLE

INSERT 0 2

CREATE TABLE

INSERT 0 2

DELETE FROM team_member_tag tmt
                WHERE tmt.tag_id NOT IN
                (SELECT t.id FROM tag t WHERE t.slug = 'first-tag' )

DELETE 1

SELECT * FROM team_member_tag

id	team_member_id	tag_id
89263	68893	3742

SELECT 1

fiddle