Nested inclusion produces wrong SQL

flashspys commented 4 years ago

We have the following layout: Category.hasMany(Datapoint), Datapoint.belongsTo(User). Hierarchy is enabled on Category. I want to query now a Category, with all their subcategories, and with all Datapoints assigned to this category and its subcategories and with all Users assigned to the Datapoints. The js looks like the following:

const datapointInclusion = {
  association: Category.DatapointsAssociation,
  required: false,
  where: {
    typeId: req.body.typeId
  },
  include: {
    association: models.Datapoint.Creator,
    attributes: models.User.WhitelistedFields,
    required: false
  },
};
const rootCategory = await Category.findByPk(req.body.categoryId, {
  include: [{
    model: models.Category,
    as: "descendents",
    hierarchy: true,
    include: datapointInclusion
  },
  {
    ...datapointInclusion,
    separate: true
  }
  ],
});

The SQL generated is the following:

SELECT
    "Category"."id",
    "Category"."name",
    "Category"."createdAt",
    "Category"."updatedAt",
    "Category"."hierarchyLevel",
    "Category"."parentId",
    "descendents"."id" AS "descendents.id",
    "descendents"."name" AS "descendents.name",
    "descendents"."createdAt" AS "descendents.createdAt",
    "descendents"."updatedAt" AS "descendents.updatedAt",
    "descendents"."hierarchyLevel" AS "descendents.hierarchyLevel",
    "descendents"."parentId" AS "descendents.parentId",
    "descendents->Categoryancestor"."CategoryId" AS "descendents.Categoryancestor.CategoryId",
    "descendents->Categoryancestor"."ancestorId" AS "descendents.Categoryancestor.ancestorId",
    "descendents->datapoints"."id" AS "descendents.datapoints.id",
    "descendents->datapoints"."quantity" AS "descendents.datapoints.quantity",
    "descendents->datapoints"."timestamp" AS "descendents.datapoints.timestamp",
    "descendents->datapoints"."meta" AS "descendents.datapoints.meta",
    "descendents->datapoints"."createdAt" AS "descendents.datapoints.createdAt",
    "descendents->datapoints"."updatedAt" AS "descendents.datapoints.updatedAt",
    "descendents->datapoints"."categoryId" AS "descendents.datapoints.categoryId",
    "descendents->datapoints"."creatorId" AS "descendents.datapoints.creatorId",
    "descendents->datapoints"."typeId" AS "descendents.datapoints.typeId",
    "descendents->datapoints"."shiftId" AS "descendents.datapoints.shiftId",
    "descendents->datapoints->creator"."id" AS "descendents.datapoints.creator.id",
    "descendents->datapoints->creator"."username" AS "descendents.datapoints.creator.username",
    "descendents->datapoints->creator"."firstname" AS "descendents.datapoints.creator.firstname",
    "descendents->datapoints->creator"."lastname" AS "descendents.datapoints.creator.lastname",
    "descendents->datapoints->creator"."preferredName" AS "descendents.datapoints.creator.preferredName",
    "descendents->datapoints->creator"."email" AS "descendents.datapoints.creator.email",
    "descendents->datapoints->creator"."language" AS "descendents.datapoints.creator.language",
    "descendents->datapoints->creator"."initial_page" AS "descendents.datapoints.creator.initial_page",
    "descendents->datapoints->creator"."style" AS "descendents.datapoints.creator.style",
    "descendents->datapoints->creator"."active" AS "descendents.datapoints.creator.active",
    "descendents->datapoints->creator"."siteId" AS "descendents.datapoints.creator.siteId",
    "descendents->datapoints->creator"."teamId" AS "descendents.datapoints.creator.teamId",
    "descendents->datapoints->creator"."pictureId" AS "descendents.datapoints.creator.pictureId",
    "descendents->datapoints->creator"."phone" AS "descendents.datapoints.creator.phone"
FROM
    "public"."Category" AS "Category"
    LEFT OUTER JOIN ("public"."Categoriesancestors" AS "descendents->Categoryancestor"
    INNER JOIN "public"."Category" AS "descendents" ON "descendents"."id" = "descendents->Categoryancestor"."CategoryId") ON "Category"."id" = "descendents->Categoryancestor"."ancestorId"
    LEFT OUTER JOIN "Datapoint" AS "descendents->datapoints" ON "descendents"."id" = "descendents->datapoints"."categoryId"
        AND "descendents->datapoints"."typeId" = 1
    LEFT OUTER JOIN "public"."User" AS "datapoints->creator" ON "datapoints"."creatorId" = "datapoints->creator"."id"
    AND "datapoints->creator"."active" = TRUE
WHERE
    "Category"."id" = 6;

The problem happens in the last LEFT OUTER JOIN so the join where the user information is appended to the datapoints. There are two problems:

The ON condition is "datapoints"."creatorId" = "datapoints->creator"."id". Unfortunately "datapoints" is not a valid table. The correct alias descendents->datapoints from the LEFT OUTER JOIN above was not used in this ON condition.
The alias datapoints->creator itself is wrong. It should be descendents->datapoints->creator, as the field selection above is suggesting.

Is there a hotfix available? Is this a problem in this repo or from sequelize?

overlookmotel commented 4 years ago

Sorry for slow reply - I just saw your issue.

This looks like a problem with Sequelize rather than sequelize-hierarchy. To find out, just remove the hierarchy: true option and try running the query. If it still fails, it's Sequelize causing the problem.

Please let me know...

overlookmotel commented 4 years ago

Did you have any luck discovering if this is a problem with sequelize-hierarchy or Sequelize itself? If it turned out to be Sequelize, could you close this issue please?

flashspys commented 4 years ago

Our code evolved around this problem, I don't have any oppertunities to test this, sorry.

overlookmotel commented 4 years ago

No problem. Thanks for letting me know.

overlookmotel / sequelize-hierarchy

Nested inclusion produces wrong SQL #215