Query breaks after adding skip and take

[FelipeTaiarol]

Issue type:
[X] question

Database system/driver:
[X] postgres

TypeORM version:
[X] 0.2.7

I have these entities:

@Entity('TABLE_1')
export class Table1{
  @PrimaryColumn({name: "ID"})
  id: number;
  @Column({name: "NAME"})
  name: string;
}

@Entity('TABLE_2')
export class Table2{
  @PrimaryColumn({name: "ID"})
  id: number;
  @Column({name: 'TABLE_1_ID'})
  table1Id: number;
}

And I get the expected result when I execute this select:

connection.createQueryBuilder().select(['t1.id', 't1.name'])
             .from(Table1, 't1')
             .addSelect('count(1)', 'count')
             .leftJoin(Table2, 't2', 't1.id = t2.table1Id')
             .groupBy('t1.id').addGroupBy('t1.name')
             .orderBy('count').getMany()

Generated query and result:

SELECT 
     "t1"."ID" AS "t1_ID", "t1"."NAME" AS "t1_NAME", count(1) AS "count" 
FROM 
    "read"."TABLE_1" "t1" 
LEFT JOIN 
    "read"."TABLE_2" "t2" 
ON 
   "t1"."ID" = "t2"."TABLE_1_ID" 
GROUP BY 
     "t1"."ID", "t1"."NAME" 
ORDER BY 
     count ASC

[ Table1 { id: '3', name: 'A' },
  Table1 { id: '2', name: 'b' },
  Table1 { id: '1', name: 'a' } ]

But If I try to paginate it using skip and take:

return connection.createQueryBuilder().select(['t1.id', 't1.name'])
             .from(Table1, 't1')
             .addSelect('count(1)', 'count')
             .leftJoin(Table2, 't2', 't1.id = t2.table1Id')
             .groupBy('t1.id').addGroupBy('t1.name')
             .orderBy('count')
             .skip(0).take(2) // only this line changed
            .getMany()

This is the query and the error that I get:

SELECT DISTINCT 
    "distinctAlias"."t1_ID" as "ids_t1_ID", "distinctAlias".count 
FROM 
     (SELECT 
          "t1"."ID" AS "t1_ID", "t1"."NAME" AS "t1_NAME", count(1) AS "count" 
       FROM 
             "read"."TABLE_1" "t1" 
       LEFT JOIN 
                  "read"."TABLE_2" "t2" 
       ON "t1"."ID" = "t2"."TABLE_1_ID") "distinctAlias" 
ORDER BY 
   "distinctAlias".count ASC, "t1_ID" ASC 
LIMIT 2

 'column "t1.ID" must appear in the GROUP BY clause or be used in an aggregate function',

[serranoarevalo]

Happening to me too. Any workaround?

[akosasante]

I'm also having this problem. Posting some info about my code, hopefully that helps narrow it down.

This is basically the query I'm trying to run:

connection
            .createQueryBuilder("customer")
            .addSelect("COUNT(job.id)", "jobCount")
            .where({company})
            .groupBy("customer.id")
            .orderBy(`customer."companyName"`)
            .leftJoin("customer.jobs", "job")
            .take(2)
            .skip(4);

This returns this query with .getSql(), which looks how I would expect it to (with no LIMIT/OFFSET clauses, which I understand is due to how take/skip work):

SELECT "customer"."id" AS "customer_id",
       "customer"."companyName" AS "customer_companyName",
       "customer"."dateCreated" AS "customer_dateCreated",
       "customer"."dateModified" AS "customer_dateModified",
       "customer"."address" AS "customer_address",
       "customer"."contact" AS "customer_contact",
       "customer"."companyId" AS "customer_companyId",
       Count("job"."id") AS "jobCount"
FROM   "customer" "customer"
       LEFT JOIN "job" "job"
              ON "job"."customerId" = "customer"."id"
WHERE  "customer"."companyId" = $1
GROUP  BY "customer"."id"
ORDER  BY customer."companyName" ASC 

However, if I try to get any results, using .getMany(), it fails. To do the skip and take it runs this query:

SELECT DISTINCT "distinctAlias"."customer_id" AS "ids_customer_id",
                "distinctAlias"."customer_companyName"
FROM   (SELECT "customer"."id" AS "customer_id",
                      "customer"."companyName" AS "customer_companyName",
                      "customer"."dateCreated" AS "customer_dateCreated",
                      "customer"."dateModified" AS "customer_dateModified",
                      "customer"."address" AS "customer_address",
                      "customer"."contact" AS "customer_contact",
                      "customer"."companyId" AS "customer_companyId",
                      Count("job"."id") AS "jobCount"
        FROM   "customer" "customer"
               LEFT JOIN "job" "job"
                      ON "job"."customerId" = "customer"."id"
        WHERE  "customer"."companyId" = $1) "distinctAlias"
ORDER  BY "distinctAlias"."customer_companyName" ASC,"customer_id" ASC
LIMIT  2 offset 4;

This should return the appropriate "id"s from the customer table so that another query can run to select just those rows from the original query. However, we get a an error: error: column "customer.id" must appear in the GROUP BY clause or be used in an aggregate function.

This query would work if the GROUP BY clause I indicated in my original query was included in the subquery generated by TypeORM. I believe the ORDER BY clause should also go inside the subquery. Is there any way to force this to happen, or should I just be using limit and offset in this kind of case?

[ghost]

@akosasante have you solved your problem? If yes then can you please describe how. Because I getting same response from query when try to use LeftJoin with take and skip.

[akosasante]

@deeptechuz Nope, I just ended up using limit and offset in the end :/

[ghost]

@akosasante Thanks bro, I guess currently no solution available for this issue. I will do same 😞

[panzelva]

Having similar problem here - except my error is column reference "<some_column>" is ambiguous

Hacked for now by replacing take and skip by limit and offset

Edit: My problem happened when select was placed before leftJoinAndSelect - when placed after, problem disappeared

[ghost]

Having similar problem here - except my error is column reference "<some_column>" is ambiguous

Actually this isn't a bug. When you use join you should define alias with columns:

    let data = await getRepository('A')
         .createQueryBuilder('a')
         .leftJoin('a.B', 'b')

         // it won't work
         .where('id=:id', {id:1234}) // here id is ambiguous because there are two tables

         // instead use this
         .where('a.id=:id', {id:1234}) // similarly you can use it for "b" as well
         .getMany();

[tamer-mohamed]

it works for me when I add the join condition to leftJoin

await getRepository('A')
         .createQueryBuilder('a')
         .leftJoin('a.B', 'b', 'a.id = b.a')   // NOTE: I've added the join condition here
          .... 
         .getMany()

[vanjalalos]

it works for me when I replace leftJoin with leftJoinAndSelect

[imnotjames]

Agreed - needs leftJoinAndSelect.

For other questions, please check out the community slack or check TypeORM's documentation page on other support avenues - cheers!

[averri]

I'm facing the same issue as described here, using leftJoinAndSelect do not solve the issue.

This is my method find, using skip and take which does not take effect, TypeORM creates the query without take and skip:

  async find(search: string, options: Paginate) {
    const qb = this.postRepository
      .createQueryBuilder('p')
      .innerJoin('p.owner', 'u', 'u.id = p.ownerId')
      .innerJoin('mentor', 'm', 'u.id = m.userId')
      .select(['p.*',
        'u.username as "ownerUsername"',
        'u.name as "ownerName"',
        'u.avatar as "ownerAvatar"',
        'm.service as "ownerService"'
      ])
      .take(options.limit)
      .skip(options.offset)

    if (search) {
      qb.where('p.title ilike :sq', {sq: `%${search}%`})
      qb.orWhere('p.text ilike :sq', {sq: `%${search}%`})
    }
    console.info(qb.getQueryAndParameters());
    return qb.stream();
  }

Versions:

"pg": "^8.7.1",
"typeorm": "^0.2.34"