<p>As of Craft 2 all information about Parent - Child relations are stored in the <code>craft_structureelements</code> table. But it's not like a hierarchy (child has the parent id) so it can be quite confusing at the first moment. 
When you take a look into the <code>ElementService</code> you'll see the following how the <code>getChildren()</code> parameter from your <code>ElementCriteriaModel</code> is converted into a <code>Query</code></p>

<pre><code>if ($criteria-&gt;descendantOf)
{
    $query-&gt;andWhere(
        array('and',
            'structureelements.lft &gt; :descendantOf_lft',
            'structureelements.rgt &lt; :descendantOf_rgt',
            'structureelements.root = :descendantOf_root'
        ),
        array(
            ':descendantOf_lft'  =&gt; $criteria-&gt;descendantOf-&gt;lft,
            ':descendantOf_rgt'  =&gt; $criteria-&gt;descendantOf-&gt;rgt,
            ':descendantOf_root' =&gt; $criteria-&gt;descendantOf-&gt;root
        )
    );

    if ($criteria-&gt;descendantDist)
    {
        $query-&gt;andWhere('structureelements.level &lt;= :descendantOf_level',
            array(':descendantOf_level' =&gt; $criteria-&gt;descendantOf-&gt;level + $criteria-&gt;descendantDist)
        );
    }
}
</code></pre>

<p>The <code>ancestorDist</code> is the <code>level</code> of the child. If you only want to have direct children you can ignore that part. <code>$criteria-&gt;descendantOf</code> is actually the <code>BaseElementModel</code> of your current element -> <code>$element-&gt;getChildren</code> . <code>$criteria-&gt;descendantOf-&gt;rgt</code> is the value in <code>craft_structureelements</code> for the certain Id. Imagine <code>element</code> is your current element - your <code>where</code> could be like</p>

<pre><code>WHERE [....] AND (structureelements.lft &lt; element.lft AND structureelements.rgt &gt; element.rgt AND structureelements.root = element.root) 
</code></pre>

<p>And the <code>getParent()</code> function will be treated with </p>

<pre><code>if ($criteria-&gt;ancestorOf)
{
    $query-&gt;andWhere(
        array('and',
            'structureelements.lft &lt; :ancestorOf_lft',
            'structureelements.rgt &gt; :ancestorOf_rgt',
            'structureelements.root = :ancestorOf_root'
        ),
        array(
            ':ancestorOf_lft'  =&gt; $criteria-&gt;ancestorOf-&gt;lft,
            ':ancestorOf_rgt'  =&gt; $criteria-&gt;ancestorOf-&gt;rgt,
            ':ancestorOf_root' =&gt; $criteria-&gt;ancestorOf-&gt;root
        )
    );

    if ($criteria-&gt;ancestorDist)
    {
        $query-&gt;andWhere('structureelements.level &gt;= :ancestorOf_level',
            array(':ancestorOf_level' =&gt; $criteria-&gt;ancestorOf-&gt;level - $criteria-&gt;ancestorDist)
        );
    }
}
</code></pre>

CC BY-SA 3.0
2018-03-23T18:47:43.407Z
FWIW, Craft is using the nested set model for storing hierarchy/order for structured elements in the database: https://en.wikipedia.org/wiki/Nested_set_model
Brad Bell
CC BY-SA 3.0
2018-03-23T12:58:54.580Z
2018-03-23T12:58:54.580Z
Robin Schambach
<p>I am writing an API which directly connects to Craft database and retrieves data.  I need to get the categories hierarchy.  But I can not find how parent and child categories are related in the database, therefore I can't write an SQL query to retrieve it. 
Can anyone help with this?</p>

CC BY-SA 3.0
2018-03-23T12:29:41.607Z
Is there are certain reason why you want to create a custom query rather than using inbuilt functions?
Robin Schambach
CC BY-SA 3.0
2018-03-23T12:56:45.883Z
I am building an api to be used by a third party app and I need to dump all data in json format for the app to be able to use it.  
I got everything done.  This is the only bit I can't figure out.
Jahanzeb Khan
CC BY-SA 3.0
2018-03-23T13:05:08.153Z
That does not really answer my question since you are still able to build the `$criteriaModel` by Craft and convert the `Models` into JSON format. However... see my answer
Robin Schambach
CC BY-SA 3.0
2018-03-29T15:21:12.157Z
I am not that familiar with craft and twig,  so I decided to make an api with php directly interacting with database.
Jahanzeb Khan
CC BY-SA 3.0
2018-03-29T15:27:17.480Z
I see.. Just a suggestion: it's faster to get used to 4-5 lines with Craft than 40 lines of pure PHP.
Robin Schambach
<categories><database>
How are parent and child categories related in Craft database?
# How are parent and child categories related in Craft database?

(Asked by: username_0 on 2018-03-23)

I am writing an API which directly connects to Craft database and retrieves data. I need to get the categories hierarchy. But I can not find how parent and child categories are related in the database, therefore I can't write an SQL query to retrieve it.
Can anyone help with this?

**Tags:** categories, database

> **Comments:**
> **username_1**: Is there are certain reason why you want to create a custom query rather than using inbuilt functions?  
> **username_0**: I am building an api to be used by a third party app and I need to dump all data in json format for the app to be able to use it.   I got everything done.  This is the only bit I can't figure out.  
> **username_1**: That does not really answer my question since you are still able to build the `$criteriaModel` by Craft and convert the `Models` into JSON format. However... see my answer  
> **username_0**: I am not that familiar with craft and twig,  so I decided to make an api with php directly interacting with database.  
> **username_1**: I see.. Just a suggestion: it's faster to get used to 4-5 lines with Craft than 40 lines of pure PHP.  

---

## 1 answers

### Answer by username_1 on 2018-03-23. Score: 1 | Accepted answer

As of Craft 2 all information about Parent - Child relations are stored in the craft_structureelements table. But it's not like a hierarchy (child has the parent id) so it can be quite confusing at the first moment. 
When you take a look into the ElementService you'll see the following how the getChildren() parameter from your ElementCriteriaModel is converted into a Query
if ($criteria->descendantOf)
{
    $query->andWhere(
        array('and',
            'structureelements.lft > :descendantOf_lft',
            'structureelements.rgt < :descendantOf_rgt',
            'structureelements.root = :descendantOf_root'
        ),
        array(
            ':descendantOf_lft'  => $criteria->descendantOf->lft,
            ':descendantOf_rgt'  => $criteria->descendantOf->rgt,
            ':descendantOf_root' => $criteria->descendantOf->root
        )
    );

    if ($criteria->descendantDist)
    {
        $query->andWhere('structureelements.level <= :descendantOf_level',
            array(':descendantOf_level' => $criteria->descendantOf->level + $criteria->descendantDist)
        );
    }
}

The ancestorDist is the level of the child. If you only want to have direct children you can ignore that part. $criteria->descendantOf is actually the BaseElementModel of your current element -> $element->getChildren . $criteria->descendantOf->rgt is the value in craft_structureelements for the certain Id. Imagine element is your current element - your where could be like
WHERE [....] AND (structureelements.lft < element.lft AND structureelements.rgt > element.rgt AND structureelements.root = element.root) 

And the getParent() function will be treated with 
if ($criteria->ancestorOf)
{
    $query->andWhere(
        array('and',
            'structureelements.lft < :ancestorOf_lft',
            'structureelements.rgt > :ancestorOf_rgt',
            'structureelements.root = :ancestorOf_root'
        ),
        array(
            ':ancestorOf_lft'  => $criteria->ancestorOf->lft,
            ':ancestorOf_rgt'  => $criteria->ancestorOf->rgt,
            ':ancestorOf_root' => $criteria->ancestorOf->root
        )
    );

    if ($criteria->ancestorDist)
    {
        $query->andWhere('structureelements.level >= :ancestorOf_level',
            array(':ancestorOf_level' => $criteria->ancestorOf->level - $criteria->ancestorDist)
        );
    }
}

> **Comments:**
> **username_2**: FWIW, Craft is using the nested set model for storing hierarchy/order for structured elements in the database: https://en.wikipedia.org/wiki/Nested_set_model  

---

