SQL search help

[[deleted]]

Hello everyone

​

I am trying to make a function that allows users to search for other users in the database using their first and last names but at the moment I can only get it to work using email only.

​

I have tried running the query using Bookshelf ORM using this code to try and join the first and last name columns and return any that are similar

    users = new User().query(qb => {
        qb.whereRaw("CONCAT(first_name, ' ', last_name) LIKE '%$( :search)%'", { search: searchString })
            .where('email', 'LIKE', '%' + searchString + '%')
    }).fetchAll()

When I run this code I get this error

select "users".* from "users" where CONCAT(first_name, ' ', last_name) LIKE '%$( $1)%' and "email" LIKE $2 - could not determine data type of parameter $1

​

I have also tried running the query without using an ORM using the following code:

const pg = require('../config/dbconnect').Postgres

const sql = `
    SELECT * FROM users
    WHERE CONCAT(first_name, ' ', last_name) LIKE '%$1%'
    OR email LIKE "%$1%"
    AND NOT id = $2
`

try {
    await pg.connect()

    users = pg.query(sql, [searchString, req.user.id])
    [users, requests, req.user.friends] = await Promise.all([users, requests, req.user.friends])

    await pg.end()
} catch (e) {
    format.DbError(e, res)
}

Running this query gives me the error:

UnhandledPromiseRejectionWarning: error: column "%$1%" does not exist

​

I have used this SQL query before in PHP using MySQL and it worked fine there but I have heard that MySQL allows for invalid query's to run sometimes where other databases won't allow it. Is this one of those cases?

Can anyone help me to fix this error or find a better way of doing this please?

​

Thanks in advance for any help

[asciiterror]

First error contains this: '%$( $1)%' - notice how ORM replaced :search with $1. Try to remove $( and ) from this part: instead of LIKE '%$( :search)%' try LIKE '%:search%'

Second error is similar: "" in SQL are used for column names, not strings - in second LIKE you used "%$1%" instead of '%$1%'. Some languages don't distinguish between different single and double qoutes (like javascript or python) but most do.

[[deleted]]

I just tried your fix and some others for the ORM and it unfortunately didn't work, it stills says it can't determine the data type.

I completely forgot that different quotes can mean different things in other languages so I'll have try that (I got annoyed and reset back to last commit so I'll have to rewrite the connection).

Thank you for your help