How to INSERT INTO with SELECT statement for columns with FOREIGN KEY in Node.js with mysql package

Question

I'm trying to INSERT INTO my table idea a new record based on values I'm getting from POST. The tricky part is that I need to fill a foreign key id bases on my value name. My values are valid I'm sure, the problem is the statement.

This is for a Node.js app working with mysql package from NPM (https://www.npmjs.com/package/mysql).

My columns are idea.name, idea.short_description, idea.description, idea.difficulty and category_id.

The statement I want to do is "INSERT INTO idea SET name = '', short_description = '', description = '', difficulty = '', category_id = (SELECT id FROM category WHERE name = '')"

The select part is the problem because I tried to attach a default number (1) and it's working.

I have to deal with mysql package that is:

db.query('INSERT INTO idea SET name = ?, short_description = ?, description = ?, difficulty = ?, category_id = 1', [name, short_description, description, difficulty, category_id], (err, result) => {
if (err) throw err
cb(result)
})

I would like something looking like this:

db.query('INSERT INTO idea SET name = ?, short_description = ?, description = ?, difficulty = ?, category = (SELECT id FROM category WHERE name = ' + category ')', [name, short_description, description, difficulty, category], (err, result) => {
if (err) throw err
cb(result)
})

I expect to get my foreign key id thanks to my foreign key name. So I try to find category.id with category.name.

Thanks in advance !

Answer 1

Seems like your problem is that you want to ran a sub-query when doing the INSERT. This is what I'm seeing in your code:

INSERT INTO idea
SET name = ?
  , short_description = ?
  , description = ?
  , difficulty = ?
  , category = (
      SELECT id
      FROM category
      WHERE name = ?
    )

So then why don't split the INSERT+SELECT as a two-steps procedure, first SELECT and then INSERT

Using this approach you would need two queries:

1. SELECT

SELECT id
FROM category
WHERE name = ?

2. INSERT

INSERT INTO idea
SET name = ?
  , short_description = ?
  , description = ?
  , difficulty = ?
  , category = ?

Also a nice side-effect is that you have a place to decide what happens when the category id is not found.