Is there a way to get the raw SQL query like getSql() in typeorm?

[reececasey]

Hi all, great lib, really helpful in my current project. As per the question, looking to get the raw sql generated. Reason being I am doing alot of advanced geospatial queries in postgis but I use the crud service to filter the data prior to this. Currently I am using it by asking for just the id's to be returned, then passing those to the main query. However, this is nasty as uses node memory to hold a huge array of id's to pass to the next postgres query. If I could access the raw sql query generated by this service I could use it nicely as the WHERE to perform only one query.

I know there a multiple tickets also for extending this to allow custom sql on top, however think my queries are so big and nested for postgis that getting the raw filter would be ideal.

Happy to take other approach advice too!

Thanks in advance.

[danielwhatmuff]

+1

[FaitAccompli]

Hi @reececasey , were you able to find a workaround for this? I'm also having the same issues. Since typeorm can stream the query response, I can simply just use the converted sql and run it with queryRunner. @nestjsx/crud currently can't stream the response.

[FaitAccompli]

Hi @reececasey and @danielwhatmuff, I was able to find a workaround for this. Not entirely sure of this approach so feel free to correct me if this is bad practice.

I used TypeOrmCrudService from '@nestjsx/crud-typeorm.

So the client sends a request of type CrudRequest , then I extract parsed, options.

{ parsed, options } = request; 
const builder = await this.createBuilder(parsed, options)

from there you can now extract the raw query and the parameters using the following:

builder.getQueryAndParameters()[0];  for the query string with just the placeholders
builder.getQueryAndParameters()[1];  for the parameters 

For discussions as to why you can't get a query with substituted parameters here's a good thread [https://github.com/typeorm/typeorm/issues/4855]

Hope this helps!

[gkbrk7]

Actually, there is a workaround to do this. Let's assume we have model object containing id property and we want to construct complex query by using id parameter. You can create subquery by using type-orm built-in methods like:

const subQuery = await getConnection()
          .getRepository(Entity)
          .createQueryBuilder()
          .andWhere(`id = :id`);
          .select(`geojson -> 'properties' ->> 'name',  id`)
          .getSql();
           
const query = await getConnection()
        .getRepository(Entity)
        .createQueryBuilder()
        .andWhere(
              `(geojson -> 'properties' ->> 'name', id) < (${subQuery})`,
        )
        .addOrderBy(... ) 
        .setParameter('id', model.id);