Parent ID 7 not found in the result set

[justraman]

I am using:

"sequelize": "^5.22.3", "feathers-sequelize": "^6.2.0",(for feathersJs) "sequelize-hierarchy": "^2.0.4"

I have several models but two are important as I assume.

Unit Model:

This is a unit model a unit can have multiple units I implemented using sequelize-hierarchy. Each unit can have users (including children's units too.)

Feed Model:

Feed (like facebook feed) here a user can post anything which can be share by selecting a unit/team/users.

My goal is to show feeds to the limited user who belongs to the feed Eg. someone shares a feed to the Parent unit then every user belongs to that unit or their child unit can see the post.

Query:

{
  where: {
    companyId: 2,
    isDraft: false,
    createdAt: { [Sequelize.Op.lt]: 1606811687360 },
    [Sequelize.Op.or]: [
        {
          "$peoples.id$": { [Sequelize.Op.eq]: context.params.user.id },
        },
        {
          "$units.users.id$": { [Sequelize.Op.eq]: context.params.user.id },
        },
        {
          "$units.descendents.users.id$": {
            [Sequelize.Op.eq]: context.params.user.id,
          },
        },
        {
          "createdById": { [Sequelize.Op.eq]: context.params.user.id },
        },
        {
          "$teams.peoples.id$": { [Sequelize.Op.eq]: context.params.user.id },
        },
      ]
  },
  order: [ [ 'createdAt', 'DESC' ] ],
  limit: 20,
  offset: 0,
  distinct: true,
  subQuery: false,
  include: [{ model: "users", as: "reads", required:false, attributes: ["id"] },
        { model: "users", as: "bookmarks", required:false, attributes: ["id"] },
        {
          model: "users",
          as: "createdBy",
          attributes: [
            "id",
            "firstName",
            "lastName",
            "profilePicture",
            "jobTitle",
          ],
        },
        {
          model: "units",
          attributes: ["id", "parentId"],
          required:false,
          through: {
            attributes: [],
          },
          include: [
            {
              model: "units",
              as: "descendents",
              required:false,
              hierarchy: true,
              attributes: ["id", "parentId"],
              include: [
                {
                  model: "users",
                  required:false,
                  through: {
                    attributes: [],
                  },
                  attributes: [],
                },
              ],
            },
            {
              model: "users",
              through: {
                required:false,
                attributes: [],
              },
              attributes: [],
            },
          ],
        },
        {
          model: "teams",
          attributes: ["id"],
          required:false,
          include: [
            {
              model: "users",
              as: "peoples",
              required:false,
              attributes: [],
            },
          ],
        },
        {
          model: "comments",
          attributes: [
            "text",
            "id",
            "likes",
            "likesCount",
            "parentId",
            "createdById",
            "createdAt",
            "updatedAt",
          ],
          include: [
            {
              model: "users",
              as: "createdBy",
              attributes: [
                "id",
                "firstName",
                "lastName",
                "jobTitle",
                "profilePicture",
              ],
            },
          ],
        },
        {
          model: "users",
          as: "peoples",
          required:false,
          attributes: [],
        }]
}

The above query works fine except it only returns two items at a time.

however count is 6, changing offset doesn't do anything same two records. if changing offset to >=6 I'm getting Parent ID 7 not found in the result set.

[justraman]

UPDATE: after deleting all data in unitsancestor everything is now working fine.

[justraman]

@overlookmotel Please help me out with how can I avoid this issue in the future? is there any way to remove the ancestor table

[overlookmotel]

The ancestor table is essential to the operation of this plugin. It stores the hierarchy of the records. So, even if for some reason it seemed like deleting all the data from the unitsancestor table solved the problem, I'm afraid that was co-incidental. You'll definitely get other problems after this.

You'll need to use .rebuildHierarchy() to re-create the deleted data in the ancestors table (see docs).

I think the problem with your initial query was the use of limit. Problem is that in a query which joins in other tables with a one-to-many association, that results in multiple table rows for each record. So the limit clause acts on the combined table rows, and you get back less results than you expected.

Your query is pretty complicated, so here's a simpler example which is hopefully illustrative of the problem:

A person can own zero, one, or many cars. So Person is associated to Car as one-to-many.

So you write a query which says "give me the first 3 people and details of all their cars" with something like:

await Person.findAll( {
  include: Car,
  limit: 3
} );

The SQL query you need it to produce is:

SELECT p.id AS `id`, p.name AS `name`, cars.id AS `cars.id`, cars.type AS `cars.type`
FROM (
  SELECT id, name
  FROM persons
  LIMIT 0, 3
) AS p
LEFT JOIN cars ON cars.ownerId = p.id

However, Sequelize may not be smart enough to get this right and may produce something more like:

SELECT person.id AS `id`, person.name AS `name`, cars.id AS `cars.id`, cars.type AS `cars.type`
FROM persons
LEFT JOIN cars ON cars.ownerId = p.id
LIMIT 0, 3

The difference is that the first query will give what you asked for - the first 3 people and all of their cars, no matter how many cars that is - but with the 2nd query, the limit is on combined table rows (i.e. the first 3 person-cars), so if the first person has 3 cars, the result you'll get is only 1 person with 3 cars.

This doesn't precisely answer your problem, but I hope gives you a starting point for figuring out what's wrong. I'd suggest looking at the raw SQL queries Sequelize is producing, and figuring out where it's putting the LIMIT clause.

I suspect this issue relates to Sequelize's handling of limit, rather than a bug in sequelize-hierarchy, so I'm going to close this. But feel free to come back if it turns out I'm wrong.