技术问询：从同一分类获取分类与子分类双slug

Hey there! Let's work through this problem to get that exact path format you're needing. First, I'll assume some standard table structures (if your fields are named differently, just swap them out to match your setup):

• Categories table: Has id (primary key), slug (the category alias you need), and parent_id (links to the parent category's ID—top-level categories usually have this set to NULL or 0)
• Items table: Has id (primary key), category_id (links to the category the item belongs to), plus whatever other item details you have

1. Basic Query (For Items in Subcategories) #

If all your items are in subcategories (not directly under top-level categories), this simple join will do the trick:

SELECT 
    CONCAT(parent.slug, '/', child.slug, '/', items.id) AS full_path
FROM 
    Items items
JOIN 
    Categories child ON items.category_id = child.id
JOIN 
    Categories parent ON child.parent_id = parent.id;

What this does: #

• First JOIN: Links each item to its direct subcategory, grabbing the subcategory's slug
• Second JOIN: Uses the subcategory's parent_id to connect to its top-level parent category, getting that parent slug
• CONCAT: Stitches everything together into the category/sub-category/items format you want (replace items.id with items.slug if your items have their own slug field!)

2. Handle Top-Level Category Items #

If some items are directly under top-level categories (no subcategory), we need to adjust to avoid missing those entries. Use LEFT JOIN and COALESCE to handle edge cases:

SELECT 
    CONCAT(
        parent.slug, 
        COALESCE(CONCAT('/', child.slug), ''), 
        '/', items.id
    ) AS full_path
FROM 
    Items items
LEFT JOIN 
    Categories child ON items.category_id = child.id
LEFT JOIN 
    Categories parent ON COALESCE(child.parent_id, child.id) = parent.id;

Quick breakdown: #

• LEFT JOIN: Makes sure we don't exclude items that are directly in top-level categories
• COALESCE(CONCAT('/', child.slug), ''): If there's no subcategory, this adds an empty string instead of breaking the path—so you'll get category/items for those cases
• COALESCE(child.parent_id, child.id): For top-level categories, we treat the category as its own parent, so we still grab the correct top-level slug

3. If You Need Item Slugs Instead of IDs #

Just swap out items.id with items.slug in either query above if your Items table has a slug field for the items themselves. That'll give you category/sub-category/item-slug instead!

内容的提问来源于stack exchange，提问作者trulex