How are parent and child categories related in Craft database?

Question

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?

Answer 1

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)
        );
    }
}