Exporting WordPress pages in menu order

The original title for this post was “How to print the Internet in the correct order”, but my humor can fall a bit flat some times so I thought I better stick to a more descriptive title.

We recently had a project at work that required us to print out the contents of one of our sites.  This is an annual project that has around 20 contributors and in the past has always started out being edited and designed in Adobe InDesign and then “ported” to the web.  This year we decided to flip the process on it’s head and have all of the contributors write their sections on the site and then export the contents and do the layout in InDesign.

Luckily InDesign supports XML imports and maps tags to styles very well.  Also in our favor, WordPress natively exports in WXR format.  The problem we ran into is that by default WordPress exports in “post id” order which is usually somewhat chronological.  Due to the security practices in our shared hosting environment writing a one-off plugin (or using any existing plugin) would have to go through so many approvals that the project would be unable to meet our deadline to get to print.  That meant that the WXR export was essentially useless to us since the editor and designer would have to spend as much time formatting and editing as the “old” way and we were trying to improve the process.

Adding to our pain was the fact that our shared hosting also restricted our database access.  I knew if I could get access to the database I would be able to write a query that would provide the data (even if in SQL format) in the correct order.  So I set about building such a query in the hopes that I would be able to convince our systems administrator to run it and provide me the output.  Here is what I came up with:

a.post_title title,
a.post_content content
m.menu_order as self_order,
p.menu_order as parent_order,
g.menu_order as gp_order,
WHEN m.post_parent = 0 THEN m.menu_order * 100
ELSE case
when g.menu_order is null then m.menu_order * 10 + p.menu_order * 100
else m.menu_order + p.menu_order * 10 + g.menu_order* 100
END AS parentId
wp_posts m
LEFT JOIN wp_posts p
ON m.post_parent = p.ID
left join wp_posts g
on p.post_parent = g.ID
where m.post_type = 'page' and m.post_status = 'publish'
ORDER BY parentId) a

In order to test it I ran it on my local development environment and that’s when a lightbulb went off in my head.  I could import the WXR import to my local development environment and have full access to the database!

So in the end, that is what ended up happening…the contributors wrote their articles, I exported the WXR, imported the WXR, ran the SQL locally, then used MySQL Workbench to export the results as an XML file that InDesign could import.

All-in-all it was a fairly pain-free process and we’re hoping next year to refine it even more.





One response to “Exporting WordPress pages in menu order”

  1. […] is going to be a quick one, I should have included it in my post on “Printing the Internet” but I […]

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.