Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

database - Recursive MySQL Query with relational innoDB

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...