title: How to generate a nested array of objects from an array of objects in nested set model?
body: <p>I'm trying to create a function that can convert a <a href="http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/" rel="nofollow noreferrer">nested set model</a> array of objects to a normal nested array of objects within array of objects. </p>
  
  <p>Currently, I'm not satisfied with my temporary solution that is limited to a depth of 2. Basically it is server-side controller using some Knex:</p>
  
  <pre><code>const getCategories = (res, db) =&gt; {
    db.raw(`
      SELECT child.id, child.name, child.path
      FROM product_category parent
      JOIN product_category child
      ON child.lower BETWEEN parent.lower AND parent.upper
      WHERE parent.id = 1
        AND
          (
            SELECT COUNT(*)
            FROM product_category node
            WHERE child.lower BETWEEN node.lower AND node.upper
              AND node.lower BETWEEN parent.lower AND parent.upper
          ) = 2 
      ORDER BY child.id
    `)
    .then(categories =&gt; {
      if (categories.rows.length) {
        const categoryPromises = categories.rows.map(category =&gt; {
          return db.raw(`
            SELECT child.id, child.name, child.path
            FROM product_category parent
            JOIN product_category child
            ON child.lower BETWEEN parent.lower AND parent.upper
            WHERE parent.id = ${category.id}
            AND
              (
                SELECT COUNT(*)
                FROM product_category node
                WHERE child.lower BETWEEN node.lower AND node.upper
                  AND node.lower BETWEEN parent.lower AND parent.upper
              ) = 2 
          `)
          .then(subcategories =&gt; {
            return { ...category, subcategories: subcategories.rows }
          })
        })
  
        return Promise.all(categoryPromises)
        .then(products =&gt; {
          res.json(products)
        })
      } else {
        res.status(400).json("No categories")
      }
    })
  }
  </code></pre>
  
  <p>The schema is here: <a href="http://sqlfiddle.com/#!17/a20af" rel="nofollow noreferrer">http://sqlfiddle.com/#!17/a20af</a></p>
  
  <p>Nested set model is a way of handling hierarchical data that encloses the children node into boundaries (commonly called <code>lft</code> and <code>rgt</code>). So if the <code>lft</code> and <code>rgt</code> values of <code>node1</code> are between the <code>lft</code> and <code>rgt</code> value of <code>node2</code>, it means that <code>node1</code> is a children of <code>node2</code> (I hope that cleared things up). </p>
  
  <p>For example, I have the following array:</p>
  
  <pre><code>const categories = [
    {
        id: 1,
        name: "Products",
        lft: 1,
        rgt: 22
    },
    {
        id: 2,
        name: "Boats",
        lft: 2,
        rgt: 15
    },
    {
        id: 3,
        name: "Rescue Boats",
        lft: 3,
        rgt: 4
    },
    {
        id: 4,
        name: "Dive Boats",
        lft: 5,
        rgt: 6
    },
    {
        id: 5,
        name: "Tamarans",
        lft: 7,
        rgt: 8
    },
    {
        id: 6,
        name: "Dragon Boats",
        lft: 9,
        rgt: 10
    },
    {
        id: 7,
        name: "Kayaks",
        lft: 11,
        rgt: 12
    },
    {
        id: 8,
        name: "Speedboats",
        lft: 13,
        rgt: 14
    },
    {
        id: 9,
        name: "Other Products",
        lft: 16,
        rgt: 21
    },
    {
        id: 10,
        name: "Slides",
        lft: 17,
        rgt: 18
    },
    {
        id: 11,
        name: "Buoys",
        lft: 19,
        rgt: 20
    }
  ]
  </code></pre>
  
  <p>I want it to have an output something like this (or an array if there are multiple root nodes):</p>
  
  <pre><code>{
    id: 1,
    name: "Products",
    subcategories: [
      {
        id: 2,
        name: "Boats",
        subcategories: [
          {
            id: 3,
            name: "Rescue Boats"
          },
          {
            id: 4,
            name: "Dive Boats"
          },
          {
            id: 5,
            name: "Tamarans"
          },
          {
            id: 6,
            name: "Dragon Boats"
          },
          {
            id: 7,
            name: "Kayaks"
          },
          {
            id: 8,
            name: "Speedboats"
          }
        ]
      },
      {
        id: 9,
        name: "Other Products",
        subcategories: [
          {
            id: 10,
            name: "Slides"
          },
          {
            id: 11,
            name: "Buoys",
          }
        ]
      }
    ]
  }
  </code></pre>