How to insert array elements into db only if they do not already exist

Question

Here is my db schema:

CREATE TABLE technologies (
    technologyName VARCHAR(50) NOT NULL PRIMARY KEY
);

CREATE TABLE videos (
    videoId             INT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title               VARCHAR(300) NOT NULL,
    url                 VARCHAR(300) NOT NULL,
    description         VARCHAR(300) NOT NULL,
    channelName         VARCHAR(300) NOT NULL
);

CREATE TABLE technology_video_map (
    videoId        INT,
    technologyName VARCHAR(50),

    PRIMARY KEY (videoId, technologyName),
    FOREIGN KEY (videoId) REFERENCES videos(videoId),
    FOREIGN KEY (technologyName) REFERENCES technologies(technologyName)
);

I want the user to submit a video:

var input = {
  title: 'Grunt makes your web development better!',
  url: 'https://www.youtube.com/watch?v=TMKj0BxzVgw',
  description: 'If you\'re not using a task runner/build system like Grunt or Gulp...',
  channelName: 'LearnCode.academy',
  technologies: ['Grunt', 'JavaScript']
};

And I want to insert it into the db. Inserting only the video is easy enough for me:

var technologies = input.technologies; // save for later
delete input.technologies;
connection.query('INSERT INTO videos SET ?', input, function (err, result) {
    var videoId = result.insertId;
});

(connection comes from the brilliant node-mysql)

What I am having trouble with, is inserting the technologies into the db. I have identified that the first step is:

1. Insert elements in input.technologies into the technologies table if they do not already exist.

I can only imagine doing this using a for loop or something which is yucky.

The second step, I think, is:

2. Insert a new record into the junction table, technology_video_map from the connection.query callback, as we need the videoId.

Again, I cannot imagine an idiomatic way to do this using SQL. Can someone please guide me?

Answer 1

You can use INSERT IGNORE and join your input.technologies array. This will insert records that do not exist and will ignore records that already exist

var query = "INSERT IGNORE INTO technologies (technologyName) VALUES ('" + input.technologies.join("'),('") + "')";