Recursive MySQL Query with relational innoDB

Question

I've got a table structure like (simplified):

content

 - id_content
 - my_string1
 - ...

content_has_content

  - id_content
  - id_subcontent

topic_has_content

  - id_topic
  - id_content

Any topic can have multiple 'content', any 'content' can have multiple 'subcontent' (instance of content). With a given id_topic I'd like to recieve a list of all my_string1 from the linked contents, subcontents, subcontents-of-subcontents, and so on.

I understand "WITH" is not working for mysql but cannot find a nice recursive alternative.

Thanks daniel

Answer 1

There is no recursion in MySQL and also the result you would get would be flat (no structure). The best way is still a while loop in PHP, Java or whatever programming language you use.

The query could look like this:

SELECT C.*, CHC.ID_SUBCONTENT
FROM CONTENT C
LEFT OUTER JOIN CONTENT_HAS_CONTENT CHC ON CHC.ID_CONTENT = C.ID_CONTENT
WHERE C.ID = ?
... // you get the idea

and in PHP you could repeat the query with the next sub_content_id, until ID_SUBCONTENT is null

Answer 2

Solution

PosgreSQL, Oracle, MS-SQL, ... have WITH RECURSIVE to handle such data structures. It internally uses a while loop to get all the parent ids for the current row (we need the child's here instead)

This can achieved in MySQL too, you can create a stored procedure and reproduce the same behavior

Assumed/DDL used

content_has_content has entries as

content_1
  content_2
    content_3
      content_4

I pass the id_content as 2 and get the output as

  content_2
    content_3
      content_4

All of the above are descendants of content_2

Fire call content_details(2); to get all the child rows for the passed id_content

SQL

###### Stored Routine
DELIMITER //  
drop procedure IF EXISTS `content_details`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `content_details`(`_idcontent` INT)
    LANGUAGE SQL
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
DECLARE  temp_content_ids varchar(200);
DECLARE idcontent, idcontent2 integer;

SET temp_content_ids= _idcontent;
SET idcontent       = _idcontent;
SET idcontent2      = _idcontent;

WHILE idcontent2 IS NOT NULL DO 
    SET idcontent = NULL;
    SELECT id_subcontent, CONCAT(id_subcontent,',',temp_content_ids) INTO idcontent, temp_content_ids FROM content_has_content WHERE id_content = idcontent2;
    SET idcontent2 = idcontent;
END WHILE;  

SELECT my_string1 FROM content WHERE FIND_IN_SET( id, temp_content_ids );
END//

What I basically do is run a while loop until I have the last child_id, store these ids in a comma separated string format and then fire a query to get all the rows which have a id present in the var I just created

Note: There are chances you could be having invalid values within your tables, such as row having a id_subcontent, which points to its own id_content, which could cause a never ending loop within the procedure, to avoid such situations, you can use a counter and limit the nesting to say around 50 (or any value as per your requirement) and raise a exception if that limit is surpassed

Some data to play with..

###### DLL Statements
CREATE TABLE content
( id_content int,
my_string1 varchar(200));

CREATE TABLE content_has_content
( id_content int,
  id_subcontent  int);

CREATE TABLE  topic_has_content
( id_topic int,
 id_content int);


INSERT INTO content VALUES (1, 'content_1'), (2, 'content_2'), (3, 'content_3'), (4, 'content_4');
INSERT INTO content_has_content VALUES (1, 2), (2, 3), (3, 4);
INSERT INTO topic_has_content VALUES (1, 1);

Hope this helps..