How to query the categories table and get a resultset with my stored order?

Question

In my Joomla project, I have one main category which contains a few subcategories. I have rearranged the ordering in the backend by drag and drop.

How can I query the database to check the ordering?

$db = JFactory::getDbo();
$sql = "select c2.* from #__categories c, #__categories c2
where c.id={$catid} and c.lft<c2.lft and c2.rgt<c.rgt";
$db->setQuery($sql);
$categories = $db->loadObjectList();

I am using this code to get all subcategories, but I'm not able to arrange them by order like I had done in the backend.

I have had a look, but there is no ordering column in the categories table.

So where Joomla is storing the ordering?

Answer 1

In #__categories table ordering field is named as lft. So, this column saves the ordering of the categories.

If you want to look more into code side then you can see:

administrator/components/com_categories/models/categories.php

And check the function populateState(), there ordering is defined.

In your query, you just need to add an order by clause and modify it like:

$sql = "select c2.* from #__categories c, #__categories c2 where c.id={$catid} and c.lft<c2.lft and c2.rgt<c.rgt order by c.lft";

Hope this helps