LB4 - Filter where two columns concat equals to string

Question

I want to filter my User table where the concatenation of two columns are equal to a given string. In this particular case, the SQL query should be like this:

SELECT * FROM User WHERE CONCAT(name, ' ', lastNames) LIKE '%PARTIAL FULLNAME%'

I'm trying to do so using LB4 and the MySQL Connector, but I can't find a way to do it without sending the raw query

Answer 1

Hello from the LoopBack team

LoopBack's query language supports only a subset of features offered by different databases, we are pretty much limited to common operators that are supported by most databases (both SQL and NoSQL).

We don't support computed values (like CONCAT(name, ' ', lastNames)).

I believe sending a raw SQL query is the only options, see Executing database commands for help.

Just make sure to use a prepared SQL statement to avoid SQL injection attacks, something along the following lines:

const result = await repository.execute(
  'SELECT * FROM User WHERE CONCAT(name, ' ', lastNames) LIKE ?',
  [
    `%${partial} ${fullName}%`,
  ],
);