0

Here's my sample table using this query :

select term_taxonomy_id
     , parent 
  from wp_term_taxonomy
 where term_taxonomy_id in (174,371,493,45,401);

enter image description here

371 is parent of 45 and 45 is parent of 401 (371->45->401).

I searched but couldn't find any real recursive solution whatever I tried could only get the child in one level for real.

I want to use something like this at first:

select term_taxonomy_id
     , parent 
  from wp_term_taxonomy
 where term_taxonomy_id in (174,371,493);

right now it gives :

enter image description here

I want to change it to support recursive selection of children, since it has 371 I want it to get its child 45 and append it and since 45 is parent of 401 I want also 401 to get appended.

174 and 493 are not parents so the result should be :

enter image description here

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Steve Moretz
  • 2,758
  • 1
  • 17
  • 31
  • Which version of MySQL are you using? Versions prior to 8.0 do not support recursion – Giorgos Betsos Jan 12 '21 at 09:25
  • I'm using mamp pro right now but I want it to work for all the servers in the future and version 8.0 isn't that much used yet right?What can be done in alternative? – Steve Moretz Jan 12 '21 at 09:28
  • Using SQL, perhaps a stored procedure. With a query you can only go back to a predefined number of levels using multiple joins – Giorgos Betsos Jan 12 '21 at 09:32
  • I can generate sql because I'm using php so that will be okay I'd suppose.If I can get the maximum level using a query and then rebuild the next one to do multiple joins and all it could totally work.Any ideas how to get how many levels at most I have? – Steve Moretz Jan 12 '21 at 09:34
  • If you do not explicitly store this information, then determining the max levels requires the same recursive solution that you would need to get the children. Pls see the answers to the duplicate question on managing hierarchical data both in mysql v5.x and 8.x. – Shadow Jan 12 '21 at 09:50
  • Note that the linked answers relate to a question dating back to 2013. MySQL has come a long way since then, so I think it's fair that the OP is furnished with a more contemporary solution. That said, see [Why should I provide an MCRE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) - and note that with the exception of the opening line of Pink Floyd's Wish You Were Here, no sentence was ever improved by the inclusion of 'So' at its beginning. – Strawberry Jan 12 '21 at 09:51
  • Yeah I saw that link but it gives two pairs of answers for different mysql versions which is kind of stupid I have never been dependent on the mysql version for my apps.And it is using only one ID as parent not multiple ids like in my example. – Steve Moretz Jan 12 '21 at 09:56
  • Tried the link suggestion and still not recursive it only gets one level and that's it. – Steve Moretz Jan 12 '21 at 17:31
  • @Strawberry the linked duplicate question is indeed from 2013, but that does not really matter, since it asks the same question with the same data structure as this question. Moreover, the accepted answer is maintained, includes recursive CTE solution for v8.0 and was last edited about a month ago. In short, the question may be old, but the answers are kept up-to-date. No to mention the fact that the OP asked for v5.7 solution and then the new techniques don't apply anyway. – Shadow Jan 12 '21 at 19:48
  • @Strawberry If you think that there are better or different solutions to go through a hierarchical dataset in mysql, then I would rather you answer in the old question in order to have the possible solutions in one place, not scattered across multiple questions. – Shadow Jan 12 '21 at 19:49
  • That answer was limited due to id must be more than parent ID that was not my case, still searching for the right answer. – Steve Moretz Jan 12 '21 at 19:51
  • This worked finally : https://stackoverflow.com/a/40085734/10268067 – Steve Moretz Jan 12 '21 at 20:02

0 Answers0