Angular noob Angular noob - 18 days ago 5
Javascript Question

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

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:


  1. 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

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("'),('") + "')";