Checking inherited attributes in an 'ancestry' based SQL table
- by Brendon Muir
I'm using the ancestry gem to help organise my app's tree structure in the database. It basically writes a childs ancestor information to a special column called 'ancestry'. The ancestry column for a particular child might look like '1/34/87' where the parent of this child is 87, and then 87's parent is 34 and 34's is 1.
It seems possible that we could select rows from this table each with a subquery that checks all the ancestors to see if a certain attribute it set. E.g. in my app you can hide an item and its children just by setting the parent element's visibility column to 0.
I want to be able to find all the items where none of their ancestors are hidden. I tried converting the slashes to comma's with the REPLACE command but IN required a set of comma separated integers rather than one string with comma separated string numbers.
It's funny, because I can do this query in two steps, e.g. retrieve the row, then take its ancestry column, split out the id's and make another query that checks that the id is IN that set of id's and that visibility isn't ever 0 and whala! But joining these into one query seems to be quite a task. Much searching has shown a few answers but none really do what I want.
SELECT * FROM t1 WHERE id = 99;
99's ancestry column reads '1/34/87'
SELECT * FROM t1 WHERE visibility = 0 AND id IN (1,34,87);
kind of backwards, but if this returns no rows then the item is visible.
Has anyone come across this before and come up with a solution. I don't really want to go the stored procedure route. It's for a rails app.
