Fetching distinct rows from multiple includes with sequelize

Question

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.

score 1 · Answer 1 · answered Apr 25 '22 at 19:28

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']],
  ...
})

answered Apr 25 '22 at 19:28

Emma

8,518
1
18
35

It still throws the same error and query being produced is `SELECT "product"."id", DISTINCT "product"."id" AS "product.id" FROM "my_schema". ....` It adds `"product"."id"` in the beginning. – confusedWarrior Apr 26 '22 at 09:03
What if I use `group: ['product.id']` ? Is it same using distinct here, as I am not using any aggregate function with group by? – confusedWarrior Apr 26 '22 at 10:09
1

Could you double check if you have double brackets `[[...]]`? That could happen if you have single bracket. If you are already using double brackets, could you copy and paste the code again in the question (current code has mismatching brackets)? – Emma Apr 26 '22 at 14:18
1

`group` works as well if you are only fetching the `product.id` in the result. In many cases `group` performs better than `distinct`. I would recommend you to look up `group vs distinct` as there are many discussions around it. – Emma Apr 26 '22 at 14:24
1

Also, my apologies that it seems Postgres _allows_ `DISTINCT(col)` syntax, although it has no effect on the parenthesis. ref: https://stackoverflow.com/questions/3408037/distinct-function-not-select-qualifier-in-postgres Issues are not about the parenthesis but something else. – Emma Apr 26 '22 at 14:30

Fetching distinct rows from multiple includes with sequelize

1 Answers1