Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how to make a like search in postgresql and node js

Tags:

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);             }         );     }); 
like image 758
Jose Sosa Avatar asked Oct 19 '13 22:10

Jose Sosa


People also ask

How do I use like keyword in PostgreSQL?

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.

Can I use node js with PostgreSQL?

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.

How do I do a wildcard search in PostgreSQL?

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.


2 Answers

an ES6 solution would be something like:

client.query('select * from users where user_name Ilike $1', [`%${req.query.q}%`], (err1, result) => { ... 
like image 24
Nir O. Avatar answered Oct 04 '22 01:10

Nir O.


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:

  1. Add the % wildcards in the client code.
  2. Concatenate the % 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.

like image 52
mu is too short Avatar answered Oct 04 '22 00:10

mu is too short