Getting all parent rows in one SQL query

问题:

I have a simple MySQL table thats contains a list of categories, level is determined by parent_id:

id  name    parent_id
---------------------------
1   Home        0
2   About       1
3   Contact     1
4   Legal       2
5   Privacy     4
6   Products    1
7   Support     1

I'm attempting to make a breadcrumb trail. So i have the 'id' of the child, I want to get all available parents (iterating up the chain until we reach 0 "Home"). There could be any number or child rows going to an unlimited depth.

Currently I am using an SQL call for each parent, this is messy. Is there a way in SQL to do this all on one query?

回答1:

Adapted from here:

SELECT T2.id, T2.name
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 5, @l := 0) vars,
        table1 h
    WHERE @r <> 0) T1
JOIN table1 T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC

The line @r := 5 is the page number for the current page. The result is as follows:

1, 'Home'
2, 'About'
4, 'Legal'
5, 'Privacy'

回答2:

Awesome answer by Mark Byers!

Maybe a bit late to the party, but if you also want to prevent an infinite loop when id = parent_id (i.e. when data has been corrupted somehow), you can expand the answer like this:

SELECT T2.id, T2.name
FROM (
    SELECT
        @r AS _id,
        @p := @r AS previous
        (SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 5, @p := 0, @l := 0) vars,
        table1 h
    WHERE @r <> 0 AND @r <> @p) T1
JOIN table1 T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC

回答3:

In addition to the above solutions:

post
-----
id
title
author

author
------
id
parent_id
name


[post]

id  | title | author |  
----------------------
1   | abc   | 3      |


[author]

| id    | parent_id | name  |   
|---------------------------|
| 1     | 0         | u1    |
| 2     | 1         | u2    |
| 3     | 2         | u3    |
| 4     | 0         | u4    |

an author including parents can have an access to the post.

I want to check whether author has an access to the post.

Solution:

give the post author's id and return all its authors and author's parents

SELECT T2.id, T2.username 
FROM (
    SELECT @r AS _id, 
        (SELECT @r := parent_id FROM users WHERE id = _id) AS parent_id,
        @l := @l + 1
    FROM
        (SELECT @r := 2, @l := 0) vars, 
        users h     
    WHERE @r <> 0) T1 JOIN users T2 
ON T1._id = T2.id;

@r := 2 => assigning value to @r variable.

回答4:

I think, there's no easy way to do that, using one query.

I would recommend to take a look at Nested Sets, that seems to fit your needs.

回答5:

If You have slug instead of id then simply run sub-query to find id of child category.
Table - categories
| id | parentId | slug |
|-------------------------|
| 1  |    0      |    u1    |
| 2  |    1      |    u2    |
| 3  |    2      |    u3    |
| 4  |    0      |    u4    |

SELECT T2.id, T2.slug
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parentId FROM categories WHERE id = _id) AS parentId,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := (SELECT id FROM categories WHERE slug = 'u3'), @l := 0) vars,
        categories h
    WHERE @r <> 0) T1
JOIN categories T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC

回答6:

AFAIK no.

This Sitepoint article may help you.

You could retrieve all the elements with one query, store it in an array and then iterate, as explained here and here