Re: recursive search

On 09/10/2016 01:01 AM, mireero wrote:
> Hello folks,
>
> I have a table of elements.
> Each of these elements can be a parent of another element.
>
> For example:
>
> "ElementA" is:
> - parent of "ElementB"
> - parent of "ElementC"
>
> "ElementB" has no chidren
>
> "ElementC" is:
> - parent of "ElementD"
> - parent of "ElementA"
>
> "ElementD" is:
> - parent of "ElementE"
>
> "ElementE" has no chidren
>
> Etc.
>
> You can notice that "ElementC" is parent of his parent "ElementA"(unlike
> in real world).
> So any element can be a parent of any other element (except directly
> himself).
>
> We have 2 tables, one which list all the elements (table "element"), and
> another for relationships (table "relation"):
>
>
> create database test;
> use test;
> create table element(id int primary key, element varchar(8));
> insert into element (id,element) values
> (1,'ElementA'),(2,'ElementB'),(3,'ElementC'),(4,'ElementD'),(5,'ElementE');
> create table relation (parent int, child int);
> insert into relation (parent, child) values (1,2),(1,3),(3,4),(3,1),(4,5);
>
> mysql> select * from element;
> +----+----------+
> | id | element |
> +----+----------+
> | 1 | ElementA |
> | 2 | ElementB |
> | 3 | ElementC |
> | 4 | ElementD |
> | 5 | ElementE |
> +----+----------+
> 5 rows in set (0.00 sec)
>
> mysql> select * from relation;
> +--------+-------+
> | parent | child |
> +--------+-------+
> | 1 | 2 |
> | 1 | 3 |
> | 3 | 4 |
> | 3 | 1 |
> | 4 | 5 |
> +--------+-------+
> 5 rows in set (0.00 sec)
>
>
> Maybe you already have an idea of the question!
>
> For a given element, I'd like to get all the descendants (and
> descendants of descendants) of this element.
> 2 possibilities:

If doing this with a SQL query, without use of store procedures or cursors, then you need to decide how many "generations" you want to track by left joining the relation table multiple times. The more times you join it, the slower it will become.

This type of queries are a lot better suited for a graph-database, if you use MariaDB, then you can use the oqgraph engine, this is an in memory graph database, so you need to populate it before you make the query, and next time you restart the database service all data is gone.

If you want a more p resistant graph-database, look at neo4j, support for it you will find at their own forums.

> Now the question:
> Is there a way to achieve this just using sql ?

Yes, but not an effective way to do it and would need a recursive call of SP with help of cursors.

https://dev.mysql.com/doc/refman/5.7/en/cursors.html https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html

If this search it the main important feature in your project, then go for a graph-database instead.

-- 

 //Aho