CODEWITHSUNDEEP
javascriptsqlnode.jspostgresqlsequelize.js
ewool
ewool

Reputation: 147

sequelize how to select child of childs in a single table?

I have the following table in a postgresql database

id | name       |active| parentId
--------------------------------
1  | food       | true | null
2  | soft drink | true | 1
3  | canned food| true | 1
4  | sweet      | true | 1
5  | candy      | true | 4
6  | chocolate  | true | 4
7  | chemical   | true | null

As you can see they are connected with a parent-child relationship by their Ids

like: food -> sweet -> candy, chocolate

I want to write a query with sequelize which will return the parent and all associated children.

Let's say I choose food, the query should return all the rows where the parentId equals to the id 1, then return all the rows where parentId equals 2,3,4 (the food's children), then return all the rows where parentId equals 5,6 (sweet's children) etc, as long as there are children.

Is it doable in a single where object?

db.ItemGroup.findAll({
  where: {
    // query
  }
});

Upvotes: 2

Views: 2105

Answers (1)

Vivek Doshi
Vivek Doshi

Reputation: 58593

you can use : sequelize-hierarchy.js

Here you have to define the model like this :

var folder = sequelize.define('folder', {
    name: Sequelize.STRING,
    parentId: {
        type: Sequelize.INTEGER,
        hierarchy: true // <------- HERE
    }
});

And I think , this is what you are looking for :

folder
.findAll({ hierarchy: true }) // <------- HERE
.then(function(results) {
    // results = [
    //  { id: 1, parentId: null, name: 'a', children: [
    //      { id: 2, parentId: 1, name: 'ab', children: [
    //          { id: 3, parentId: 2, name: 'abc' }
    //      ] }
    //  ] }
    // ]
});

You can checkout the link , there are more examples to show the possible things you can do with this library.

NOTE : You can also do this without the plugin, but tit will be very length way , this is more cleaner.

Upvotes: 2

Related Questions