1

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?

  • Is there are certain reason why you want to create a custom query rather than using inbuilt functions? – Robin Schambach Mar 23 '18 at 12:29
  • 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 Mar 23 '18 at 12:56
  • 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 Mar 23 '18 at 13:05
  • I am not that familiar with craft and twig, so I decided to make an api with php directly interacting with database. – Jahanzeb Khan Mar 29 '18 at 15:21
  • 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 Mar 29 '18 at 15:27
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)
        );
    }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.