typeorm: Querybuilder generating syntax errors

Issue type:

[ x] question [x ] bug report [ ] feature request [x ] documentation issue

Database system/driver:

[ ] cordova [ ] mongodb [ ] mssql [ ] mysql / mariadb [ ] oracle [ x] postgres [ ] sqlite [ ] sqljs [ ] react-native [ ] expo

TypeORM version:

[ ] latest [ ] @next [ ] ^0.2.7

QueryBuilder.getMany() and .getManyAndCount() are both wrapping my queries in a SELECT DISTINCT clause, which breaks my query when I use .orderBy. It’s deceptive, because it’s formatting the SQL different from .getSql(). The SQL from .getSql() works just fine when I run it manually.

I’m not sure why queryBuilder is wrapping my query in a SELECT DISTINCT clause… Is there a reason for this, or a way to disable it?

I’m not sure if this is a bug, question, or just something that needs better documentation.

Can .orderBy be used with queryBuilder?

My Code:

const query = this.serviceRepository.createQueryBuilder('service')
            .leftJoinAndSelect('service.type', 'service_type')
            // .where("LOWER(\"service\".\"countryName\") LIKE LOWER(:text)", {
            //     text: '%' + search.country + '%'
            // })
            // .orWhere("LOWER(\"service\".\"countryCode\") LIKE LOWER(:text)", {
            //     text: '%' + search.country + '%'
            // })
            .orderBy('\"service_countryName\"', 'ASC')
            .take(pageSettings.pageSize)
            .skip(pageSettings.pageSize * indexPage)

        const sql = await query.getSql()

        console.log('SQL OUT: ', sql)

        const services = await query.getMany()

The SQL as defined by .getSql() (Which works):

SELECT 
    "service"."id" AS "service_id", 
    "service"."created" AS "service_created", 
    "service"."updated" AS "service_updated", 
    "service"."notes" AS "service_notes", 
    "service"."active" AS "service_active", 
    "service"."associateFee" AS "service_associateFee", 
    "service"."governmentFee" AS "service_governmentFee", 
    "service"."miscMargin" AS "service_miscMargin", 
    "service"."chainGangMargin" AS "service_chainGangMargin", 
    "service"."typeId" AS "service_typeId", 
    "service"."countryId" AS "service_countryId", 
    "service"."countryCreated" AS "service_countryCreated", 
    "service"."countryUpdated" AS "service_countryUpdated", 
    "service"."countryName" AS "service_countryName", 
    "service"."countryCode" AS "service_countryCode", 
    "service_type"."id" AS "service_type_id", 
    "service_type"."created" AS "service_type_created", 
    "service_type"."updated" AS "service_type_updated", 
    "service_type"."name" AS "service_type_name", 
    "service_type"."description" AS "service_type_description" 
FROM 
    "service" "service" 
    LEFT JOIN "service_type" "service_type" ON "service_type"."id" = "service"."typeId" 
WHERE 
    LOWER("service"."countryName") LIKE LOWER($1) 
    OR LOWER("service"."countryCode") LIKE LOWER($2) 
ORDER BY 
    "service_countryName" ASC

The SQL that is actually hitting my database when using .getMany() or .getManyAndCount() (and failing)

SELECT 
    DISTINCT "distinctAlias"."service_id" as "ids_service_id", 
    "distinctAlias"."service_countryId" as "ids_service_countryId" 
FROM 
    (
        SELECT 
            "service"."id" AS "service_id", 
            "service"."created" AS "service_created", 
            "service"."updated" AS "service_updated", 
            "service"."notes" AS "service_notes", 
            "service"."active" AS "service_active", 
            "service"."associateFee" AS "service_associateFee", 
            "service"."governmentFee" AS "service_governmentFee", 
            "service"."miscMargin" AS "service_miscMargin", 
            "service"."chainGangMargin" AS "service_chainGangMargin", 
            "service"."typeId" AS "service_typeId", 
            "service"."countryId" AS "service_countryId", 
            "service"."countryCreated" AS "service_countryCreated", 
            "service"."countryUpdated" AS "service_countryUpdated", 
            "service"."countryName" AS "service_countryName", 
            "service"."countryCode" AS "service_countryCode", 
            "service_type"."id" AS "service_type_id", 
            "service_type"."created" AS "service_type_created", 
            "service_type"."updated" AS "service_type_updated", 
            "service_type"."name" AS "service_type_name", 
            "service_type"."description" AS "service_type_description" 
        FROM 
            "service" "service" 
            LEFT JOIN "service_type" "service_type" ON "service_type"."id" = "service"."typeId" 
        WHERE 
            LOWER("service"."countryName") LIKE LOWER($1) 
            OR LOWER("service"."countryCode") LIKE LOWER($2)
    ) "distinctAlias" 
ORDER BY 
    "service_countryName" ASC, 
    "service_id" ASC, 
    "service_countryId" ASC 
LIMIT 
    26 -- PARAMETERS: ["%%","%%"]

The associated logging error:

error: for SELECT DISTINCT, ORDER BY expressions must appear in select list

Thanks in advance!

About this issue

Original URL
State: open
Created 5 years ago
Reactions: 21
Comments: 23 (3 by maintainers)

Commits related to this issue

fix: wrong query on QueryBuilder containing skip, take and orderBy Closes: #2736, #3501 — committed to Hossein-s/typeorm by Hossein-s 5 years ago
fix: wrong query on QueryBuilder containing skip, take and orderBy Closes: #2736, #3501 — committed to Hossein-s/typeorm by Hossein-s 5 years ago

Most upvoted comments

It should work fine if you use offset and limit instead of skip and take cause it wont produce a subquery.

+21

garlou on Jun 14, 2019

any solution for this? I’m having the same issue. Is there a way to resolve this?

+10

tuturis on Apr 15, 2020

Also experiencing this issue. None of the provided workarounds that don’t use limit/offset work for me though – i have joins.

FoxxMD on Apr 19, 2022

Remove the methods skip, take from query builder and implement them using JS .slice works for me. like it:
const entities = queryBuilder.getMany()
const paginatedEntities = entities.slice(skip, skip + take)
If you need handle a custom column (not mapped on typeorm entity) use .getRawAndEntities() instead getMany()

Note: I think to use typeorm skip / take and JS .slice have the same time complexity, O(n)

Your time blocking the thread, bandwidth utilization, memory usage and electricity bill will also have a complexity of O(n) 🔥

Seriously though, unless you only ever have a couple of entries in your database you should never do this. Let your database crunch the data - that’s what it’s for after all.

BitPatty on Jan 3, 2022

I think @robbyemmert is right, the order by is getting added to the outside query when it should only be on the inside (from what I can tell). I can confirm that using offset and limit does work as a workaround. I was having the same issue using skip and take when implementing a pretty simple pagination based on created date. Switching to offset and limit fixed it. That being said, the docs say specifically that when implementing pagination you should use skip and take so I would be interested in a real fix to those two methods. Just a note, I also tried specifying the order on the entity itself

@Entity("myEntity", {
   orderBy: {
      createdDate: "ASC"
   }
}

and that broke it too even when no orderBy call was made to the query builder.

Mando75 on Jul 1, 2019

I could find a workaround for my case. I am using complex ORDER BY, SKIP and TAKE. I am putting my order by attribute to the select statement with .addSelect Broken code:

this.createQueryBuilder('a')
            .leftJoin(`tableB`, 'b, 'b.id=a.id')
            .skip(offset)
            .take(limit);
            .addOrderBy(
                  `(SELECT count(*) as count FROM tableC c WHERE c.some_id = a.id)`,
                  'DESC',
            )
            .getManyAndCount();

And I could fix it using

this.createQueryBuilder('a')
            .leftJoin(`tableB`, 'b, 'b.id=a.id')
            .skip(offset)
            .take(limit);
            .addSelect(
                  `SELECT count(*) as count FROM tableC c WHERE c.some_id = a.id)`,
                  'some_count',
            );
            .addOrderBy(`some_count`, 'DESC');
            .getManyAndCount();

Hope this helps

Niklas-Mezynski on Apr 13, 2023

Came across this nasty bug - left comment at this PR instead.

anodynos on Aug 5, 2019

This worked for me: https://github.com/typeorm/typeorm/issues/747#issuecomment-519553920

For some reason the combination of JOIN + ORDERBY + TAKE/SKIP 1.) fails when you use the column name instead of entity property name. For example, firstName is property name, first_name is column name. 2.) fails when you use what you would think would be the alias that typeorm creates for you (for example: table_name_column_name).

Try changing orderBy to “table_name.attributeName” - hopefully it works!

kmordan24 on Jan 20, 2022

yeah, same here. Suppose such case: because there’s no support for selecting computed columns using addSelect('COUNT(prop) as result') I got the count using subquery in add select. Sorting works great until I use skip and take methods for pagination (this is paginated table). Then, the query is wrapped into distinctAlias and I can’t select my computed field anymore, it’s hidden in wrapped select and not mapped in outer select (which would allow me to sort over the computed field). Do you have idea of how to sort on computed column together with pagination using this lib?

wojciechbator on Mar 5, 2020