I have a database with table that contains pages with their slug names. If I have urls like this:
/page/child_page_a/child_page_b/child_page_c/
I’m wondering how best to setup a route for pages that can be any number deep. I don’t want to match just the last url segment since this could be named the same in another url. Is the best solution to pull all db rows that match the last field and then match parents all the way back in each of those till I find a perfect match?
I’m trying to find the most efficient way of doing this as I don’t want to hit the database a bunch or pull all the pages from the db for comparison. Just not sure how to best do that. I also don’t want to store the whole url as the page url for a bunch of reasons (one of which is that if, for instance, ‘child_page_a’ got renamed then it’d break all the other links).
I once had the same requirement for nested slugs in the URL in an online shop and I did just what you don’t want to do - stored the whole URL path in the category table. Then resolving the route is just a matter of a simple query involving an indexed column - it’s very fast, and if you utilize mysql query cache then it’s lightning fast! I don’t know if anything can be faster than that (maybe apart from some cache solutions in memory).
As to breaking other links when renaming I simply implemented appropriate logic in the code that updates the category table and if the slug name has changed then the application scans and updates all other related slugs (actually, I think it updates all slugs because the code is simpler and still fast enough). Usually changing the name is a rare occurrence so spending a fraction of a second more in these cases doesn’t hurt anything. Of course, it depends on your use case and category table size - if updates are more frequent then reads then a different approach would be suitable.
I just came across a different method. With this example URL:
/Page1/SubPage1/SubSubPage1
You can access it via one query like so:
SELECT p1.id
FROM pages AS p1, pages AS p2, pages AS p3
WHERE p1.PageName = ‘SubSubPage1’ AND p1.parentID = p2.ID AND
p2.PageName = ‘SubPage1’ AND p2.parentID = p3.ID AND
p3.PageName = ‘Page1’
So here is the code I ended up using (where $url is equal to the url segment after the domain name):
// Get segments in reverse order
$parts = array_reverse( explode('/', $url) );
$from = $where = '';
for($i=1; $i <= count($parts); $i++){
if( $i!=1 ){
$from.= ', ';
$where.= ' AND ';
}
$from.= 'pages as p'.$i;
$where.= 'p'.$i.'.slug = ?';
if( $i < count($parts) ) $where.= ' AND p'.$i.'.parent_id = p'.($i+1).'.id';
else $where.= ' AND p'.$i.'.parent_id = 0';
}
// GET PAGE FROM DB
$q = $this->db->query('SELECT p1.* FROM '.$from.' WHERE '.$where, $parts);
I’m using CodeIgniter so in the query the $parts array values replace all the ‘?’ symbols (for those that were wondering).
This seems to be efficient enough to get it all in one query. I like that. Just a note but by doing it this why, you’d want to put an index on that slug field. That’s an area that could be slowed down in that query.