Is any parent offline in mysql adjacency list model?

Question

I have a database table that looks like this:

id | parent_id | status 1 | null | 1 2 | null | 0 3 | null | 1 4 | 2 | 1 5 | 4 | 1 

So, as you can see, we have some categories that are children, and the depth is unlimited.

What I want to know is there an easy way to find out if any parents in a particular tree have status 0?

I know if there was a fixed depth, then I could just make so many connections and then use Min(status) to find out if any of them are set to 0, but I don't know how to do it if undefined depth?

Answer 1

With the adjacency list model that you use, it is not possible, because you will need to recursively move the graph upwards, which MySQL cannot do. You should consider switching your data to a nested set model where you can easily get the root path.