Thanks Jeremy, I'll take a look at that. To be honest, I'm not sure how much effort I want to put into it as I don't really have a need for it right now. I just thought it might be interesting to look at and write up for others. It seems like this doesn't have a "generic" SQL solution that's supported across databases and so this makes it hard for someone without a pretty good database background to use.
Scott On Wed, Oct 28, 2009 at 3:45 PM, Jeremy Evans <[email protected]>wrote: > > On Oct 28, 3:12 pm, Scott LaBounty <[email protected]> wrote: > > OK, I looked at the nested sets and they look complex and I looked at the > > "with recursive" and it doesn't look like sqlite (which I use for just > > hacking around) doesn't support it. So ... any other ideas or do I just > need > > to suck it up and learn nested sets or suck it up and use a better > database. > > Nested sets are significantly more complex than trees, and I don't > know of a Sequel plugin that handles them. SQLite doesn't support > common table expressions. > > It's not that using trees in SQLite is going to cause problems, it's > just more work to get all descendants. If you look at the Advanced > Associations page on the website, it shows how to handle such a case. > > One simpler way to handle this is a text field that stores the path. > Assuming it's indexed and that prefixed searching uses the index, this > should be a fairly fast approach: > > 1 > | > ------- > | | > 2 3 > | > ------- > | | > 4 5 > > Paths would be: > > 1 - '1' > 2 - '1/2' > 3 - '1/3' > 4 - '1/3/4' > 5 - '1/3/5' > > Then if you want all descendants/ancestors of 3: > > node = Tree[3] > descendents = Tree.filter(:path.like("#{node.path}/%")).order > (:path).all > ancestors = Tree.filter(:id=>node.path.split('/').map{|x| > x.to_i}).order(:path.desc).all > > I haven't tried that, and there are probably issues with the approach, > but it may work for simple cases. > > Jeremy > > > -- Scott http://steamcode.blogspot.com/ --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sequel-talk" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en -~----------~----~----~----~------~----~------~--~---
