如何从url中查找slug名称并创建动态查询语句

Question

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 

Answer 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.