Search code examples
sqliteknex.js

insert more than 1 value using knex with raw

I've went through knex docs, and have tried every possible combination I could find with no success. However, using SQLiteStudio I was able to be successful with regular queries using more than one ?...

select * from 'articles' where LOWER("cover_page") = ? AND "cover_page" != "" AND "user_id" = ?

select * from 'articles' where LOWER("title") = ? AND "title" != "" AND "user_id" = ?

select * from 'articles' where LOWER("link") = ? AND "link" != "" AND "user_id" = ?

...In knex, I am able to get the following successful if I only use 1 ? at a time:

const doesExist = await db('articles')
    .where(db.raw('LOWER("cover_page") = ?', article.cover_page.toLowerCase()))
    .orWhere(db.raw('LOWER("title") = ?', article.title.toLowerCase()))
    .orWhere(db.raw('LOWER("link") = ?', article.link.toLowerCase()))
    .first();

Does anyone have any idea how to input the second value where AND "user_id" = ? ??

The goal is for the output to return nothing...

Solution

  • Yes you can. See also documentation link.

    The key you are missing is passing the parameters properly. See the positional array for passing the variables: .whereRaw('LOWER(cover_page) = ? AND LOWER(title) = ?', ['somepage', 'sometitle'])

    (I also suspect you should not quote your database fields in this context.)

    I haven't executed it but this should be close:

    const doesExist = await db('articles')
   .whereRaw('LOWER(cover_page) = ? OR LOWER(title) = ? OR LOWER(link) = ?',
       [article.cover_page.toLowerCase(), 
        article.title.toLowerCase(),
        article.link.toLowerCase()] ) 
   .first();

    PS: You can also use the positional array for passing parameters to raw() the same as I used it for whereRaw().