'Fetching distinct rows from multiple includes with sequelize

I have some nested joins below. I am trying to fetch distinct rows out of it.

Product.findAll({
  attributes:[sequelize.fn('DISTINCT', sequelize.col('product.id')), 'product.id']],
  include: {
     model: Course,
      attributes: [],
      required: true,
      include: {
        model: Class,
        where: {
          classId: args.classId
        },
        attributes: []
      }
  }

})

syntax error at or near "DISTINCT"

Query being produced by sequelize,

SELECT "product"."id", DISTINCT("product"."id") AS "product.id" FROM "my_schema"."product_groups" AS "product" INNER JOIN "my_schema"."courses" AS "courses" ON "product"."id" = "courses"."product_group_id" INNER JOIN "my_schema"."classes" AS "courses->classes" ON "courses"."id" = "courses->classes"."course_id" AND "courses->classes"."organization_id" = '68700940-f509-4662-975f-a3ba3382aa9b';;

Working sql query,

SELECT DISTINCT("product"."id")  FROM "my_schema"."product_groups" AS "product" INNER JOIN "my_schema"."courses" AS "courses" ON "product"."id" = "courses"."product_group_id" INNER JOIN "my_schema"."classes" AS "courses->classes" ON "courses"."id" = "courses->classes"."course_id" AND "courses->classes"."organization_id" = '68700940-f509-4662-975f-a3ba3382aa9b';

How can I product above query with sequelize to return distinct rows. I also tried distinct: true but it doesn't make any change. With this option query works and returns result but no distinct is there in the query generated.

postgresqlsequelize.js


Solution 1:[1]

The DISTINCT syntax in Postgres should look like this.

SELECT DISTINCT column AS alias FROM table;

DISTINCT is a clause and not function so you need to use literal.

Product.findAll({
  attributes:[[sequelize.literal('DISTINCT "product"."id"'), 'product.id']],
  ...
})

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Emma

Related Questions