Copying an entire menu hierarchy in Joomla
A friend of mine recently asked if we could make a quick change to his client’s Joomla site. The problem seemed simple enough: duplicate an entire section of the site and make it available under a different menu heading. For the most part, the articles being pointed to by each menu did not need to change though there were a few exceptions.
The solution seemed simple, so I decided to knock this one out myself rather than bother one of our engineers. I figured that Joomla would have the tools for accomplishing this task and that I just need to find them. Well, I figured wrong. While you can copy a menu item in Joomla, you can’t conveniently copy an entire menu hierarchy. So, I rolled up my sleeves and went to work. Three great things came out of this experience that I want to share.
First, you’ll find the entire Python script for performing this task below. All you have to do is change some of the constants and run it on your server. Please make sure you take the time to understand the code before you run it though. I did use this script to solve my problem, but your problem and setup is probably different. If you have any questions or, if you use the script and it works wonderfully, please feel free to contact me.
Second, while I started Setaris as a co-founder and coder, I haven’t done much coding over the past couple years. Nowadays, I leave it in the most capable hands of our engineers. However, as a COO and project manager, most of my results only show up in metrics. It’s good to occasionally give yourself tasks that culminate in a concrete thing - something that you can call yours. Even better if you can hold it, but let’s not get too crazy.
Finally, as managers, it is sometimes difficult to empathize with the people who are actually getting stuff done. No matter your industry, there’s real value in jumping into the trenches occasionally and helping out in whatever way you can. I guarantee your team will appreciate you for it.
import MySQLdb
import sys
#DB Settings
DB_HOST = 'host.com'
DB_NAME = 'db_name'
DB_PASS = 'db_pass'
DB_USER = 'db_username'
#Table Info
TB_PREFIX = 'tb_prefix' # leave empty if your tables aren't prefixed
MENU_TABLE = '%s_menu' % TB_PREFIX
#Misc Settings
MENU_TO_COPY = 4 # the ID of the menu to copy
def make_row_copy(row_to_copy, parent_id):
print "Copying '%s' with parent %d" % (row_to_copy[col_lookup['name']], parent_id)
sql = """
insert into %s(%s, published, parent)
select %s, 0, %d from %s
where id = %d;
""" % (MENU_TABLE, col_list, col_list, parent_id, MENU_TABLE,
row_to_copy[col_lookup['id']])
print sql
cursor.execute(sql)
row_to_copy = list(row_to_copy)
row_to_copy.append(cursor.lastrowid)
return row_to_copy
# make a connection to the mySQL DB
conn = MySQLdb.connect(host=DB_HOST, user=DB_USER, passwd=DB_PASS,
db=DB_NAME)
#get table columns and set up lookup and list
cursor = conn.cursor()
cursor.execute('show columns from %s' % MENU_TABLE)
cols = cursor.fetchall()
col_lookup = {}
col_list = []
idx = 0
for col in cols:
if col[0] not in ('id', 'published', 'parent'):
col_list.append(col[0])
col_lookup[col[0]] = idx
idx += 1
col_list = ', '.join(col_list)
# add new_id column for after the copy
col_lookup['new_id'] = idx
# get root menu
cursor.execute('select * from %s where id = %d' %
(MENU_TABLE, MENU_TO_COPY))
row = cursor.fetchone()
print row[col_lookup['name']]
# cycle through and get the children
new_row = make_row_copy(row, 0)
parents = []
current_parent = new_row
copy_queue = []
i = 0
while current_parent:
# find children of current parent
cursor.execute('select * from %s_menu where parent = %d' %
(TB_PREFIX, current_parent[col_lookup['id']]))
children = cursor.fetchall()
first_iter = True
for child in children:
if first_iter:
new_row = make_row_copy(child,
current_parent[col_lookup['new_id']])
first_iter = False
else:
copy_queue.append((child,
current_parent[col_lookup['new_id']]))
if first_iter: #no children
if len(copy_queue) == 0:
current_parent = None
else:
tmp = copy_queue.pop()
current_parent = make_row_copy(*tmp)
else:
current_parent = new_row
cursor.close ()
conn.close()
