How to find all leaf nodes?

[davesag]

I have an hierarchical collection of Category objects and wish to find just the ones that do not have children, ie the leaf nodes.

Following the documentation

The forms with { hierarchy: true } are equivalent to using folder.findAll({ include: { model: folder, as: 'children' } }) except that the include is recursed however deeply the tree structure goes.

I have tried

Category.findAll({
  hierarchy: true,
  where: { children: { [Op.or]: [{ [Op.eq]: null }, { [Op.eq]: [] }] } }
})

but get SequelizeDatabaseError: column Category.children does not exist

Trying

Category.findAll({
  hierarchy: true,
  include: [{ model: Category, as: 'children' }],
  where: { children: { [Op.or]: [{ [Op.eq]: null }, { [Op.eq]: [] }] } }
}}

gives the same error, and even just

Category.findAll({
  include: [{ model: Category, as: 'children' }],
  where: { children: { [Op.or]: [{ [Op.eq]: null }, { [Op.eq]: [] }] } }
}}

if I just do

Category.findAll({ hierarchy: true }}

I get back my expected Category hierarchy:

[
  {
    "id": 1,
    "name": "Dairy",
    "hierarchyLevel": 1,
    "parentId": null,
    "children": [
      {
        "id": 2,
        "name": "Milk",
        "hierarchyLevel": 2,
        "parentId": 1,
        "children": [
          {
            "id": 3,
            "name": "Full-Cream Milk",
            "hierarchyLevel": 3,
            "parentId": 2
          }
        ]
      }
    ]
  }
]

Where I note that the final instance does not have a children field. Maybe this is what's causing the error.

What is the recommended method to find all of the leaf instances?

[overlookmotel]

Sorry for slow reply.

I am a bit out of date with Sequelize, but last time I used it there was no way to do a NOT EXISTS query through the API.

In raw SQL, you can achieve what you're after with:

SELECT *
FROM Categories
WHERE NOT EXISTS (
    SELECT *
    FROM Categories AS Children
    WHERE Children.parentId = Categories.id
)

i.e. Give me all the records where there are no other records which reference this record as its parent.

See these two issues: https://github.com/sequelize/sequelize/issues/2787 https://github.com/sequelize/sequelize/issues/4099

This might also be useful: http://docs.sequelizejs.com/manual/tutorial/models-usage.html#top-level-where-with-eagerly-loaded-models

Please let me know if you find a solution.

[overlookmotel]

Closing as no response from OP.