0

Lets say I have a table called Tags with an id columnm name column, and a parent_id column. Many tags are nested using the parent_id column. How would I check if Tag A has Tag B as a non-direct child efficiently.

Previously I have selected all tags that have a parent_id of the current tag and then got the result and repeated for any child elements.

How would I do this more efficiently to get all tags that match a search and is a direct or non-direct child.

Thanks for the help, Jason

Jason Gallavin
  • 403
  • 4
  • 14
  • 1
    You'll want to do this: http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query –  Dec 09 '15 at 20:15
  • @DanK beat me to that, but if you can't restructure your table structure, there are not many better options than what you are already doing. – Uueerdo Dec 09 '15 at 20:16
  • Maybe these would be useful for you, http://stackoverflow.com/questions/902678/parse-an-xml-string-in-mysql http://dev.mysql.com/doc/refman/5.7/en/xml-functions.html. (I've always done XML parsing in PHP). – chris85 Dec 09 '15 at 20:17
  • @DanK I can structure the database differently if I need to. Just to make sure, this has the capability of a dynamic number of layers of children? Thanks! – Jason Gallavin Dec 09 '15 at 20:20
  • No the levels are hardcoded and not a true recursive query. I work more with Oracle and SQL Server where recursive queries are possible but according to this post MySQL doesn't offer this functionality: http://dba.stackexchange.com/questions/46127/recursive-self-joins –  Dec 09 '15 at 20:21
  • @DanK That's a bummer. In the link it mentions some workarounds, one being an adjacency list. Does that sound like a solution? I'm guessing the Oracle and SQL Server are paid software? Thanks – Jason Gallavin Dec 09 '15 at 20:28
  • @JasonGallavin the highest voted answer on the question DanK linked supports indefinite layers. The only downside is that when a child is added, much of the tree must be updated as well. Edit: I should point out I am talking about the "Nested Set" model. – Uueerdo Dec 09 '15 at 20:33

1 Answers1

0

Rather than discuss in the comments... here is what I would recommend:

  • If you want to stick with MySQL but can play with the structure of your database then absolutely this "Closure Table" pattern suggested by Bill Karwin is the way to go. It allows you to keep your data in a flat table design while abstracting the multi-level tree structure into a separate table for easy data extraction.

  • If you want to try a different Relational Database System then you might try SQL Server Express which is free from Microsoft. In full disclosure, I don't use this so I don't know what functionality is excluded (and I'm sure something is otherwise you wouldn't get it for free). So please do some research to make sure Recursive Common Table Expressions (CTEs) are available. If they are then you can use Pinal Dave's blog post for recursive SQL technique using CTEs.

  • Otherwise if you only think you will only ever have a handful of levels to work with, you can use the original suggestion and hardcode the number of levels.

Community
  • 1
  • 1