I have the following table:
id | parent_id | searchable | value
--------------------------------------------
1 | 0 | 0 | a
2 | 1 | 0 | b
3 | 2 | 1 | c
4 | 0 | 0 | d
5 | 4 | 1 | e
6 | 0 | 0 | f
7 | 6 | 0 | g
8 | 6 | 0 | h
9 | 0 | 1 | i
I need to extract all the top level records (so the ones where the parent_id = 0
).
But only the records where the parent OR one of his children is searchable (searchable = 1
)
So in this case, the output should be:
id | parent_id | searchable | value
--------------------------------------------
1 | 0 | 0 | a
4 | 0 | 0 | d
9 | 0 | 1 | i
Because these are all top-level records and it self or one of his childeren (doesn't matter how 'deep' the searchable child is) is searchable.
I am working with MySQL. I am not really sure if it is possible to write this with just one query, but I assume it should be done with a piece of recursive code or a function.
** Note: it is unknown how 'deep' the tree goes.
You will have to use stored procedure to do it.
Find all rows with searchable = 1, store their ids and parent_ids in a temp table. Then do self-joins to add parents to this temp table. Repeat until no more rows can be added (obviously better make sure tree is not cyclic). At the end you have a table only with rows that have a searchable descendant somewhere down the tree, so just show only rows with no parent (at the top).
Assuming your table is called 'my_table' this one should work:
DELIMITER //
DROP PROCEDURE IF EXISTS top_level_parents//
CREATE PROCEDURE top_level_parents()
BEGIN
DECLARE found INT(11) DEFAULT 1;
DROP TABLE IF EXISTS parent_tree;
CREATE TABLE parent_tree (id int(11) PRIMARY KEY, p_id int(11)) ENGINE=HEAP;
INSERT INTO parent_tree
SELECT id, parent_id FROM my_table
WHERE searchable = 1;
SET found = ROW_COUNT();
WHILE found > 0 DO
INSERT IGNORE INTO parent_tree
SELECT p.id, p.parent_id FROM parent_tree c JOIN my_table p
WHERE p.id = c.p_id;
SET found = ROW_COUNT();
END WHILE;
SELECT id FROM parent_tree WHERE p_id = 0;
DROP TABLE parent_tree;
END;//
DELIMITER ;
Then just calling it:
CALL top_level_parents();
will be equal to
SELECT id FROM my_table WHERE id_is_top_level_and_has_searchable_descendant