I am using node js and the module pg for connect to postrgresql i want to make a search for a database of tags but i can't make it work, in the variable tag i saved the param of the url , but the consult doesn't return any results. how can i fix?
app.get("/tags", function(req, res){ var tag = req.query.q; res.json(tag) var search = db.client.query("SELECT * FROM tags WHERE name LIKE '%$1%'", [tag], function(err, result){ res.json(result); } ); });
The PostgreSQL LIKE operator is used to match text values against a pattern using wildcards. If the search expression can be matched to the pattern expression, the LIKE operator will return true, which is 1. The percent sign represents zero, one, or multiple numbers or characters.
Essentially, node-postgres is a collection of Node. js modules for interfacing with a PostgreSQL database. Among the many features node-postgres supports include callbacks, promises, async/await, connection pooling, prepared statements, cursors, rich type parsing, and C/C++ bindings.
You construct a pattern by combining literal values with wildcard characters and use the LIKE or NOT LIKE operator to find the matches. PostgreSQL provides you with two wildcards: Percent sign ( % ) matches any sequence of zero or more characters. Underscore sign ( _ ) matches any single character.
an ES6 solution would be something like:
client.query('select * from users where user_name Ilike $1', [`%${req.query.q}%`], (err1, result) => { ...
I don't know the node.js PostgreSQL interface that well but I think I can see the problem. This is an SQL string literal that contains a numbered placeholder:
'%$1%'
The $1
inside that string won't be replaced with the value of tag
because placeholders inside strings are not placeholders at all, they're just substrings that happen to have the same form as a placeholder.
The two usual options are:
%
wildcards in the client code.%
wildcards onto the strings inside the database.The first one would look like this:
db.client.query("SELECT * FROM tags WHERE name LIKE $1", ['%' + tag + '%'], ...
and the second like this:
db.client.query("SELECT * FROM tags WHERE name LIKE '%' || $1 || '%'", [tag], ...
Use whichever approach you prefer.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With