Categories db
id name parent_id slug
----------------------------------
1 Men 0 men
2 Shoes 1 shoes
3 Sports 2 sports
4 Women 0 women
5 Shoes 4 shoes
6 Sports 5 sports
Let's say i'm on domain/category/men/shoes/sports
$last_slug = end((explode('/', Request::url())));
Here I'll can get sports as last query
Question :
How to retrieve all parents slug dynamically, so I can select something like this if the slug depth = 3?
SELECT t1.name AS level_one,
t2.name AS level_two,
t3.name AS level_three,
t3.id AS requested_id
FROM categories AS t1
LEFT JOIN categories AS t2 ON t2.parent_id = t1.id
LEFT JOIN categories AS t3 ON t3.parent_id = t2.id
WHERE t1.slug = 'men'
AND t2.slug = 'shoes'
AND t3.slug = 'sports'
LIMIT 1
The best way will be to have an additional column where you will store the complete path.
This is one of the standard ways of storing trees in db - Materialized Path.
As a bonus, you will have the ability to select all children of the current category with a single query.
It makes it a bit more tricky to correctly update paths when slugs are changed or subcategories are moved from one parent to another. But this are actions that will be performed not very often.
Dynamically building the query with joins is bad for performance, especially for such a simple task: select category by path.
Just swapping the order of the joins around a touch:-
SELECT t1.name AS level_one,
t2.name AS level_two,
t3.name AS level_three,
t1.id AS requested_id
FROM categories AS t1
LEFT JOIN categories AS t2 ON t2.id = t1.parent_id
LEFT JOIN categories AS t3 ON t3.id = t2.parent_id
WHERE t1.slug = 'sports'