database - 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