I am trying to order jos_menu items by the order in which they appear on the menu.
In other words, I am using jdatabase sql queries to fetch menu objects then iterate through them to display them.
I know that this is accomplished in other components table by the ordering column, however I can't seem to find the ordering column on the jos_menu table.
How will I accomplish this?
As @sharky already explained, Joomla's menu's use a nested structure. An excellent resource regarding Nested Structures (how they work and SQL to find nodes, paths, calculate tree depth etc) is available at: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
Edit: The page explains how the lft + rgt in nested structures work. See for example this image (made by Mike Hillyer and shown on his site).
For example: the Television node has the number 2 on the left side (lft 2) and the number 9 on the right side (rgt 9). As you can see, you can indeed use all the "lft" numbers in this structure for ordering. But you can get even more information with lft + rgt, like to calculate how many children a node has:
((rgt - lft) - 1 ) / 2 = number of children.
In this example Television has 3 children: (9 - 2) - 1 = 6 / 2 = 3 nodes.
Joomla uses the nested structures in a couple of its tables: #__assets, #__categories, #__menu, #__tags, and #_usergroups.
Before you start, you should look into Joomla's Menu library. Maybe it already does what you need.
Menu table uses nested structure. Menu items have parents and levels. Ordering is defined by lft and rgt columns. To get a flat list of items ordered by "ordering" you can order by lft.
