Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-03-23T18:47:43.407Z
Answers.Text: 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
Answers.UserDisplayName: Brad Bell
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-03-23T12:58:54.580Z
Answers.LastActivityDate: 2018-03-23T12:58:54.580Z
Answers.UserDisplayName: Robin Schambach
Body: <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>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2018-03-23T12:29:41.607Z
Comments.Text: Is there are certain reason why you want to create a custom query rather than using inbuilt functions?
Comments.UserDisplayName: Robin Schambach
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2018-03-23T12:56:45.883Z
Comments.Text: 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.
Comments.UserDisplayName: Jahanzeb Khan
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2018-03-23T13:05:08.153Z
Comments.Text: 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
Comments.UserDisplayName: Robin Schambach
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2018-03-29T15:21:12.157Z
Comments.Text: I am not that familiar with craft and twig,  so I decided to make an api with php directly interacting with database.
Comments.UserDisplayName: Jahanzeb Khan
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2018-03-29T15:27:17.480Z
Comments.Text: I see.. Just a suggestion: it's faster to get used to 4-5 lines with Craft than 40 lines of pure PHP.
Comments.UserDisplayName: Robin Schambach
Tags: <categories><database>
Title: How are parent and child categories related in Craft database?
ThreadText: # 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  
  
  ---
  
  