Sequelize: Sequelize association count with limit produces wrong query

[hassan-ahmed]

The following query produces the correct results as expected..

Title.findAll({
  attributes: [
    'id', 'name', 'overview' , 'imdbRating',
    [db.sequelize.fn('COUNT', db.sequelize.col('Subtitles.id')), 'subsCount']
  ],
  include: [{
    model: Subtitle,
    attributes: [],
    required: false
  }],
  group: ['"Title"."id"'],
  offset: 0
})

Result:

Executing (default): SELECT "Title"."id", "Title"."name", "Title"."overview", "Title"."imdbRating", 
COUNT("Subtitles"."id") AS "subsCount" 
FROM "Titles" AS "Title" 
LEFT OUTER JOIN "Subtitles" AS "Subtitles" ON "Title"."id" = "Subtitles"."TitleId" 
GROUP BY "Title"."id"  OFFSET 0;

.
.
.
but as soon as I add a limit to it, the whole query blows up and produces an error:

Title.findAll({
  attributes: [
    'id', 'name', 'overview' , 'imdbRating',
    [db.sequelize.fn('COUNT', db.sequelize.col('Subtitles.id')), 'subsCount']
  ],
  include: [{
    model: Subtitle,
    attributes: [],
    required: false
  }],
  group: ['"Title"."id"'],
  offset: 0,
  limit: 20
})

Result:

Executing (default): SELECT "Title".* 
FROM (
  SELECT "Title"."id", "Title"."name", "Title"."overview", "Title"."imdbRating", 
  COUNT("Subtitles"."id") AS "subsCount" 
  FROM "Titles" AS "Title" 
  GROUP BY "Title"."id" 
  LIMIT 20 OFFSET 0
) AS "Title" 
LEFT OUTER JOIN "Subtitles" AS "Subtitles" ON "Title"."id" = "Subtitles"."TitleId";

Error:

Unhandled rejection SequelizeDatabaseError: missing FROM-clause entry for table "Subtitles"

.
.
.
I've also tried the following:
Query:

Title.findAll({
  attributes: ['id', 'name', 'overview' , 'imdbRating'],
  include: [{
    model: Subtitle,
    attributes: [
      [db.sequelize.fn('COUNT', db.sequelize.col('Subtitles.id')), 'subsCount']
    ],
    required: false
  }],
  group: ['"Title"."id"'],
  limit: 20,
  offset: 0
})

Result:

Executing (default): SELECT "Title".*, "Subtitles"."id" AS "Subtitles.id", 
COUNT("Subtitles"."id") AS "Subtitles.subsCount" 
FROM (
  SELECT "Title"."id", "Title"."name", "Title"."overview", "Title"."imdbRating"
  FROM "Titles" AS "Title" 
  GROUP BY "Title"."id" 
  LIMIT 20 OFFSET 0
) AS "Title" 
LEFT OUTER JOIN "Subtitles" AS "Subtitles" ON "Title"."id" = "Subtitles"."TitleId" 

Error:

Unhandled rejection SequelizeDatabaseError: column "Title.id" must appear in the GROUP BY clause or be used in an aggregate function

.
.
Query:

Title.findAll({
  attributes: ['id', 'name', 'overview' , 'imdbRating'],
  include: [{
    model: Subtitle,
    attributes: [
      [db.sequelize.fn('COUNT', db.sequelize.col('Subtitle.id')), 'subsCount']
    ],
    group: ['TitleId'],
    separate: true,
    required: false
  }],
  limit: 20,
  offset: 0,
  order: [['name', 'ASC']]
})

Result:

Executing (default): SELECT "Title"."id", "Title"."name", "Title"."overview", "Title"."imdbRating" 
FROM "Titles" AS "Title" 
ORDER BY "Title"."name" 
ASC LIMIT 20 OFFSET 0;

Executing (default): SELECT "id", COUNT("Subtitle"."id") AS "subsCount" 
FROM "Subtitles" AS "Subtitle" 
WHERE "Subtitle"."TitleId" IN (24, 11, 110, 20, 127, 31, 48, 158, 169, 105, 50, 6, 35, 39, 115, 67, 124, 27, 132, 5) 
GROUP BY "TitleId" OFFSET 0;

Error:

Unhandled rejection SequelizeDatabaseError: column "Subtitle.id" must appear in the GROUP BY clause or be used in an aggregate function

.
.
I'm using
Sequelize: 3.23.3
PostgreSQL: 9.4.8

[mickhansen]

Use duplicating: false on the include as a workaround when you KNOW for a fact that it won't generate a cartesian product, this will stop the subquery from bgeing generated.

[hassan-ahmed]

Thanks, it did solve the problem.

[keyiis]

@mickhansen if i want order by aggregate field,sequelize will auto add prefix('modelName.') to aggregate field.
ex:

table1.findAll({
  include: [{
    model:table2 ,
    attributes: [
      [db.sequelize.fn('COUNT', db.sequelize.col('table2.id')), 'count']
    ],
    duplicating: false,
    required: false
  }],
  group: ['table1.id'],
  order: [['count', 'ASC']]
})

sequelize generate sql:
select sum(table2.id) as count from table1 LEFT OUTER JOIN table2 on table1.id=table2.t1id order by table1.count