Typeorm getManager().query() works only if pass hardcoded SQL query string not variables

Question

I try to make SQL requests from the server using Typeform and getManager().query(), passing to query string with SQL.

Working example from MySqlWorkbench screenshot https://prnt.sc/uad3tu and code snippet. If I copy and paste it to the code, it works just fine

    SELECT `Event`.*, `Repeats`.`start_event_at`, `Repeats`.`end_event_at`
    FROM `events` `Event` 
    LEFT JOIN `event_repeats` `Repeats` on `Repeats`.`eventId` = `Event`.`id`
    WHERE `Event`.`category` = 'fitness' 
    AND `Repeats`.`end_event_at` >= '2020-09-02T14:59:00.000Z'

But if I use variables in an interpolated string it throws an error, screenshot https://prnt.sc/uad5zy and

    SELECT `Event`.*, `Repeats`.`start_event_at`, `Repeats`.`end_event_at`
    FROM `events` `Event` 
    LEFT JOIN `event_repeats` `Repeats` on `Repeats`.`eventId` = `Event`.`id`
    WHERE `Event`.`category` = ${category} 
    AND `Repeats`.`end_event_at` >= '2020-09-02T14:59:00.000Z'

Thrown error: https://prnt.sc/uad6zs

What I do wrong and how to manage it?

spike 王建 · Accepted Answer · 2020-09-02 16:54:56Z

2

When using query method use underlying driver escaping mechanism. For mysql:

await getEntityManager().query('SELECT * FROM tbl_1 WHERE category = ?', [ 'fitness' ])

answered Sep 2, 2020 at 16:54

spike 王建

1,7647 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

spike 王建 Over a year ago

@АлександрНаумкин Thanks for you feedback, glad to help you

Danton Heuer · Accepted Answer · 2024-03-20 13:06:22Z

1

Adding to other responses, for future reference - each driver has different ways of doing that replace of variables

Oracle: query('SELECT * FROM table WHERE name = :name', [ { val: 'something' } ])
MySQL: query('SELECT * FROM table WHERE name = ?', [ 'something' ])
MSSQL: query('SELECT * FROM table WHERE name = @0', [ 'something' ])
Postgres: query('SELECT * FROM table WHERE name = $1', [ 'something' ])

Source: https://github.com/typeorm/typeorm/issues/881#issuecomment-1576624514

answered Mar 20, 2024 at 13:06

Danton Heuer

4165 silver badges12 bronze badges

Comments

Alex Aymkin · Accepted Answer · 2020-09-03 07:32:55Z

The final code looks like follows:

async getAllEvents(search_options: any, end_at, offFilters): Promise<Event[]> {

const { isOnline, isMobility, impairment, category } = search_options;

let events = null;
if (offFilters) {
  events = await getManager().query(
      ' SELECT Event.*, Repeats.start_event_at, Repeats.end_event_at ' +
            ' FROM events Event ' +
            ' LEFT JOIN event_repeats Repeats ON Repeats.eventId = Event.id ' +
            ' WHERE Event.category = ? ' +
            ' AND Repeats.end_event_at >= ?', [category, end_at])
} else {
  const online = isOnline ? 1 : 0
  const mobility = isMobility ? 1 : 0

  events = await getManager().query(
            ' SELECT Event.*, Repeats.start_event_at, Repeats.end_event_at ' +
            ' FROM events Event ' +
            ' LEFT JOIN event_repeats Repeats ON Repeats.eventId = Event.id ' +
            ' WHERE Event.category = ? ' +
            ' AND Event.isOnline = ? ' +
            ' AND Event.isMobility = ? ' +
            ' AND Event.impairment = ? ' +
            ' AND Repeats.end_event_at >= ?', [category, online, mobility, impairment, end_at])
}
return events
}

Collectives™ on Stack Overflow

Typeorm getManager().query() works only if pass hardcoded SQL query string not variables

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related