Feb
22
2014
Uncategorized

SQL to Pull WordPress Menu Info

Author An article by Vince    Comments Comments Off on SQL to Pull WordPress Menu Info

I wanted to see how WordPress stored its menus. I assumed there must be a table in the database somewhere but I couldn’t find it. Turns out that’s because menu items are stored as posts with a ‘nav_menu_item’ post type. In all there where about 4 or 5 tables the information was spread over. I wrote this query to pull it all together.

I might come back later and make some comments about what is going on but for now I just wanted a place to dump the code. 


SELECT 
	terms.name AS 'Menu Name',
	(SELECT meta_value FROM wp_postmeta 
		WHERE meta_key = '_menu_item_menu_item_parent' 
		AND post_id = termrel.object_id) AS 'Parent Menu ID',
	CASE posts.post_title
		WHEN '' THEN posts2.ID
		WHEN NULL THEN posts2.ID
		ELSE termrel.object_id
	END AS 'Post ID',
	CASE posts.post_title
		WHEN '' THEN posts2.post_title
		WHEN NULL THEN posts2.post_title
		ELSE posts.post_title
	END AS 'Menu Title',

	(SELECT meta_value FROM wp_postmeta 
		WHERE meta_key = '_menu_item_type' 
		AND post_id = termrel.object_id) AS 'Item Type',
	(SELECT meta_value FROM wp_postmeta 
		WHERE meta_key = '_menu_item_url' 
		AND post_id = termrel.object_id) AS 'Custom URL'

FROM	
	wp_term_relationships termrel
	INNER JOIN wp_term_taxonomy termtax ON termtax.term_taxonomy_id = termrel.term_taxonomy_id
	INNER JOIN wp_terms terms ON terms.term_id = termtax.term_id
	INNER JOIN wp_posts posts ON posts.ID = termrel.object_id
	LEFT JOIN 
		(SELECT pm.meta_value as MetaVal, pm.post_id
		 FROM wp_postmeta pm 
		 INNER JOIN wp_term_relationships tr ON pm.post_id = tr.object_id
		 WHERE pm.meta_key = '_menu_item_object_id') AS ItemID
		ON termrel.object_id = ItemID.post_id
	LEFT JOIN wp_posts posts2 ON ItemID.MetaVal = posts2.ID
WHERE
	termtax.taxonomy='nav_menu'
ORDER BY 
	termtax.term_taxonomy_id, posts.menu_order;
Tags: , , ,

Related Posts

About the Author:

Comments are closed.