CODEWITHSUNDEEP
javascriptsqlnode.jssqlite
Luke Chen
Luke Chen

Reputation: 63

In nodejs SQLite3 .each() method, how can I add mulitple arguments to work with multiple "WHERE ... = ?" queries?

I have a table that has a column named CRS_TITLE and I would like to search a list of course titles within that column. Right now my code only works with one argument at a time. I suppose I can write a simple loop, and during each iteration I can just call db.each() again with a new class name. I know this way is inefficient, so I would like to check all the arguments within one pass.

Things I have tried:

1) Adding another '?' in WHERE and other variations.

2) Using the new spread operator from javascript like [...classes] when passing in the arguments.

None of the above works due to syntax error.

Anyways, here is my code below.

let sqlite3 = require('sqlite3').verbose()

// open the database
let db = new sqlite3.Database('./classes.db', (err) => {
    if (err) {
      console.error(err.message);
    }
    console.log('Connected to the classes database.');
  });

let sql = `SELECT CRS_CDE course_code,
                  Column3 start_time,
                  Column4 end_time
            FROM spring_schedule
            WHERE CRS_TITLE = ?`; 
            // I want to do multiple "WHERE = ?" here.
            // with multiple arguments 

let className1 = 'Spanish I'

// I want to change the line above to this instead.
// let classNames = ['Accounting I', 'English', 'Math'...etc]


db.each(sql, [className1]/*pass an array of items to look up instead of just 1 item.*/, (err, row) => {
  if (err) {
    throw err;
  }
  console.log(`${row.course_code} ${row.start_time} - ${row.end_time}`);
  // Will use this result to update frontend.

});

// close the database connection
db.close();

EDITED: working code below.

let sql = `SELECT CRS_CDE course_code,
                  Column3 start_time,
                  Column4 end_time
            FROM spring_schedule
            WHERE CRS_TITLE IN `;

let classNames = ['Spanish I', 'Accounting II', 'College Algebra']
let where = '(?' + ',?'.repeat(classNames.length-1) + ')';
sql += where;

db.each(sql, [...classNames], (err, row) => {
  if (err) {
    throw err;
  }
  console.log(`${row.course_code} ${row.start_time} - ${row.end_time}`);
  // Will use this result to update frontend.

});

db.close();

Upvotes: 1

Views: 487

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

You may try building a dynamic WHERE IN clause using the exact number of parameters to match your input array:

let sql = `SELECT CRS_CDE course_code, Column3 start_time, Column4 end_time
           FROM spring_schedule
           WHERE CRS_TITLE IN `;
let classNames = ['Accounting I', 'English', 'Math'];
let where = '(?' + ',?'.repeat(classNames.length-1) + ')';
sql += where;

db.all(sql, [], (err, rows) => {
    if (err) {
        throw err;
    }
    rows.forEach((row) => {
        console.log(row.name);
    });
});
db.close();

Upvotes: 1

Related Questions