Storing menus in Drupal 7 databases

Tags: 

Sometimes you need code that messes with Drupal's menus. It helps if you know how Drupal stores menu information in the database.

One-level menu

Let's start by looking at a simple, one-level main menu. Suppose I have a Drupal installation, and create a Welcome page. I add the page to the main menu as I am creating it, leaving the weight set to 0, its default:

Welcome page

I do the same for two more pages: Unicorns and Rainbows. Each of them is a node, stored in the database. They're in the node table. Here's what they look like:

Three nodes

The primary key of the node table is nid (node id).

Here is the menu I end up with:

Main menu

How does Drupal remember what the menu entries are?

Making menus

Let's look at the HTML Drupal makes for the menu. It's something like this:

<ul>
  <li>
    <a href="node/12">Rainbows</a>
  </li>
  <li>
    <a href="node/11">Unicorns</a>
  </li>
  <li>
    <a href="node/10">Welcome</a>
  </li>
</ul>

Drupal stores the data needed to make the menu in the table menu_links. Here it is so far:

Menu links table, no weights.

Each row represents one menu entry. The primary key of the table is mlid, the menu item's id. The menu_name field tells Drupal which menu the entry is part of.

Each menu item is rendered as an <a> tag, as in <a href="path">text</a>. The link_path field shows the path. The link_title field shows the link's text.

Remember that the menu looks like this:

Main menu

The page I created first is at the end of the menu. The page I created next is at the start of the menu. What gives?

The items are in alphabetical order. Unless you tell Drupal otherwise, it shows menu items alphabetically.

Ordering the menu items

Suppose I go to Admin | Structure | Menus | Main menu. I see:

Menu, alphabetical order.

The items are in alpha order, just as they appear in the menu. I drag them around to get:

Menu

The main menu looks like this:

Menu

Let's see how Drupal stores this ordering information. Here's the menu_links table again:

menu_links table with with weights

Look at the weight field, over on the right. The new order is shown there.

Items are sorted alphabetically within weight. So, if several items have weights of 0, and several have weights of 1, the ones with the higher weights (1) come later. Within the 0s, Drupal shows the items alphabetically. Within the 1s, Drupal shows the items alphabetically.

The drag-and-drop code I used to rearrange the menu gives each item its own weight. There is only one item with a weight of -48. So there is no need to sort alphabetically within items of the same weight.

So far, we have a table called menu_links, with one row per menu item. Some of the table's fields are:

mlid: the primary key.

menu_name: the menu an item is in.

link_path: the menu item's path.

link_title: the text of the item.

weight: the order of items in the menu. Items with the same weight are sorted alphabetically.

Coding

How can we use our new knowledge? Suppose some nodes have the title "Zombies." We want to make sure that, if there is such a node in the main menu, it is always the first item in the menu.

Let's use a hook, so that the following pseudocode is run just before a node is saved:

if current_node.title = "zombies" then
  if current_node.menu_name = "main_menu" then
    //Find the lowest weight in the main menu.
    lowest_weight = select min(weight)

                    from menu_links
                    where menu_name = "main_menu"
    current_node.menu_weight = lowest_weight - 1
  end if
end if

That should do it.

A submenu

Things get more complex when there is a submenu.

Creating the pages

Suppose I add two pages: Good Unicorns, and Evil Unicorns. I add them under the Unicorns item on the main menu:

Submenu item

Submenu item

Notice that I gave a weight for the second one. It should come after the first in the menu.

A block for the submenu

To see the submenu, I'll use the Submenu Tree module. It can put a submenu in a block. Here is what I get when Unicorns is selected:

The submenu in a block.

So, it worked! What does the database look like?

The menu_links table

Here it is:

menu_links table with submenu items.

All of the menu items are in main-menu, as you can see from the first column. The important field is plid, which shows the mlid of each item's parent item. Recall the mlid is the unique id of each menu item.

The first three items are at the top level of the main menu. They have no parent, so their plid is 0.

The two new items are children of the Unicorns item. Unicorns has an mlid of 330. So, the plid of the new items is 330.

Now look at the weight weight field on the right. You can see the values I used when creating the two new nodes.

Coding

Now for some weird coding. We want a list of all menu items that have a child item that has the word "evil" in it.

Here's some pseudocode:

for each row in menu_links
  if current_row.link_title contains "evil" then
    if parent_mlid != 0 then
      parent_mlid = current_row.plid
      parent_title = select link_title from menu_links where mlid = parent_mlid
      print parent_title
    end if
  end if
end for

That should work.

Summary

Drupal keeps most of the data about a site in a database. That includes data about menus.

The menu_links table has the text and path of each link. The table's primary key is mlid. The order of a menu's links depends on the links' weights, and their alphabetical order.

For submenu items, the plid field stores the parent's mlid.