Query menu in WordPress

Menu set in WordPress is stored in MySQL database. The rules as followings:

1. menu set

• identified in wp_term_taxonomy with taxonomy = 'nav_menu'
• name is stored in wp_terms.name

2. Menu item could be one of the followings:

• pages, posts,
• categories, tags,
• custom links,
• items that plugin provides.

3. menu item in wp_posts table is marked as post_type = 'nav_menu_item'. And menu item in wp_posts links with category in wp_terms using wp_postmeta:

• meta_key = '_menu_item_object_id' indicates the term_id in wp_terms.
• meta_key = '_menu_item_object' identifies the object type.
• meta_key = '_menu_item_menu_item_parent' indicates the parent menu item.

4. if using category or tag as menu item, wp_terms.name is the menu label,
if using custom links, wp_posts.post_title is the menu label.

5. tag and category are treated as same way for menu. The different between tag and category is the taxonomy value, tag is post_tag, category is category.

6. category description is stored in wp_term_taxonomy.description and wp_posts.content.

Complete menu query like this:

SELECT p.menu_order order,p.ID,parent.meta_value menu_parent, 
   (CASE WHEN t.name is null THEN p.post_title ELSE t.name END) menu,
   p.post_content description
FROM wp_posts p
   LEFT JOIN wp_postmeta m on m.post_id=p.ID AND m.meta_key='_menu_item_object_id'
   LEFT JOIN wp_postmeta parent on parent.post_id=p.ID and parent.meta_key='_menu_item_menu_item_parent'
   LEFT JOIN wp_terms t on t.term_id=m.meta_value
   LEFT JOIN wp_posts pp on pp.ID=parent.meta_value AND parent.meta_value!=0
WHERE p.post_type='nav_menu_item'
order BY p.menu_order

Result

 2,145 total views,  4 views today

Author: IT Team