CODEWITHSUNDEEP
phpmysql
Unknown Error
Unknown Error

Reputation: 797

How to find slug name from url and make a dynamic query statement

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

Upvotes: 1

Views: 471

Answers (2)

dr.scre
dr.scre

Reputation: 2377

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.

Upvotes: 2

Kickstart
Kickstart

Reputation: 21533

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'

Upvotes: 0

Related Questions