Once again, I walk through the door on a Monday morning and find a task on my board that requires a bit of a hack-a-round to get the desired results. Today it was to alphabetize all categories. There are many ways to approach this problem and I’ve tried them all. We could modify collection classes to sort by name, but there are many instances of category lists on frontend and admin. We could hire an intern to drag and drop the categories on a development environment and copy the catalog_category_entity table, but that’s a time-consuming process.
So of course I wanted to write a script that could provide reproducible results every time we ran it. I also decided against using the Magento API or plain PHP because that just slows it down and all we’re doing is sorting categories. So here’s what I did:
First, I created a MYSQL Stored Procedure called “sort_categories” which utilizes the CURSOR iterator. It basically loops through the results of a SELECT query that is sorted by name and returns all IDs. An UPDATE query is then run for each ID and an incremented integer which becomes the position for that category. And it’s pretty quick, sorting 300 categories in about 10 seconds.
One quirk I noticed is that procedures fail if a selected field is not returned, I think, so I just added SELECT @id; which allowed the procedure to execute. If you know why that is, let me know!
If you’re looking for Magento Services, give us a call!