# Nested sets from database **URL:** \ **Category:** Rails\ **Created:** [November 28, 2007, 3:00pm UTC](https://www.ruby-forum.com/t/nested-sets-from-database/121500 "2007-11-28T15:00:41Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Tonkatsufan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/a4c791/32.png) [@Tonkatsufan](https://www.ruby-forum.com/u/Tonkatsufan)\ **Post date:** [November 28, 2007, 3:00pm UTC](https://www.ruby-forum.com/t/nested-sets-from-database/121500/1 "2007-11-28T15:00:41Z")
Hi, I’d like to use AR’s find to retrieve a nested dataset from the database by grouping on a column. Of course, when I group on the column, the children are not in the result set, so I was wondering what’s the best practice to achieve this. my table has data like this id week value 1 week1 1 2 week1 32 3 week1 15 4 week2 3 5 week2 31 6 week4 3 7 week4 1 etc. week is a varchar and I want to group by week, and get a result like: weeks = { “week1” =\> [1,15,32], “week2” =\> [3,31], “week4” =\> [1,3] } so, the weeks should be sorted (asc or desc), and the week’s array should also be sorted from low to high. The reason is that I would like to output an ordered list in my view: week desc, values asc. My problem is to go from the finder to a data set that will easily allow me loop over in a view. How should this be done? TIA tonkatsu ---
**Author:** ![Tonkatsufan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/a4c791/32.png) [@Tonkatsufan](https://www.ruby-forum.com/u/Tonkatsufan)\ **Post date:** [November 28, 2007, 5:09pm UTC](https://www.ruby-forum.com/t/nested-sets-from-database/121500/2 "2007-11-28T17:09:21Z")
Your task would be a lot easer if you have two tables… tblweek and tblweekvalues and a has many relationship. Then you could do: tblweek.find(:all, :order =\> :week\_name).each do |week| week.week\_values.each… But with your structure it would be something like: prev\_week tblweek.find(:all, :order =\> :week\_name, :value).each do |week| if prev\_week == week.name it s a new week… do something prev\_week = week.name else still in the old week… end ---
**Author:** ![Tonkatsufan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/a4c791/32.png) [@Tonkatsufan](https://www.ruby-forum.com/u/Tonkatsufan)\ **Post date:** [November 28, 2007, 5:55pm UTC](https://www.ruby-forum.com/t/nested-sets-from-database/121500/3 "2007-11-28T17:55:02Z")
If you prefer your structure, use the Array#group\_by method to divide up your find results: week\_info = Weeks.find(:all).group\_by{|week| week.week} group\_by yields each object to the block and then uses the results of the block to create the key for a hash. The results of that line would be exactly what you’re looking for with one notable exception: a hash is inherently unorganized so it won’t be sorted. You would either need to handle that programmatically or go a step further and convert the hash to an array (week\_info.to\_a) and then sort the array (week\_info.to\_a.sort). ---
**Author:** ![Tonkatsufan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/a4c791/32.png) [@Tonkatsufan](https://www.ruby-forum.com/u/Tonkatsufan)\ **Post date:** [November 28, 2007, 9:42pm UTC](https://www.ruby-forum.com/t/nested-sets-from-database/121500/4 "2007-11-28T21:42:53Z")
Hi, thanks for this idea. I will try it tomorrow. I had this wishful thinking that it’s possible to somehow have a “has\_many” relationship to the same table. Single-table inheritance isn’t doing the trick. Cheers Tonkatsu ---
**Author:** ![Tonkatsufan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/a4c791/32.png) [@Tonkatsufan](https://www.ruby-forum.com/u/Tonkatsufan)\ **Post date:** [November 28, 2007, 9:45pm UTC](https://www.ruby-forum.com/t/nested-sets-from-database/121500/5 "2007-11-28T21:45:49Z")
On Nov 28, 5:08 pm, “removed\_email\_address@domain.invalid” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > Your task would be a lot easer if you have two tables… > > tblweek and tblweekvalues and a has many relationship. Hmm theoretically has\_many should be possible on the same table. The task I described should be pretty common for web apps. > ``` > prev_week = week.name > > ``` > > else > still in the old week… > end Yes, that’s how it could be done the fast and dirty way - I was hoping for some Ruby-Fu way to to it in one short line 😉 Anyway - thank you for the idea Tonkatsu ---
**Author:** ![Tonkatsufan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/a4c791/32.png) [@Tonkatsufan](https://www.ruby-forum.com/u/Tonkatsufan)\ **Post date:** [November 28, 2007, 10:31pm UTC](https://www.ruby-forum.com/t/nested-sets-from-database/121500/6 "2007-11-28T22:31:35Z")
Altering your table structure, and thus your related code, seems a little drastic. Here’s another way that’s fast, and won’t break any existing code… In mysql, there is a great aggregate function that works with group by, called group\_concat that unfortunately isn’t available in all databases which is a shame really. A good description of it is here: > **[Hail to GROUP\_CONCAT!](http://db4free.blogspot.com/2006/01/hail-to-groupconcat.html)** > > This blog post has moved. Please find it at: http://www.mpopp.net/2006/01/hail-to-group\_concat/ . Assuming you are using mysql, it might be very simple to create a view in your database (as I show below) to achieve the data being returned as you need it, and then you can simply create a new model for this view so it can be searched very easily and displayed in rails when needed using AR. so, if your data is in a table called mytable: id week value 1 week1 1 2 week1 32 3 week1 15 4 week2 3 5 week2 31 6 week4 3 7 week4 1 etc. then try this query out create or replace view weeksums as select week, group\_concat(value) as weekval from mytable group by week; then generate model weeksum do note: this brings back comma delimited data, so you may need to use the string function tr to remove them if you don’t want them in. Andrew ---
**Author:** ![Tonkatsufan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/a4c791/32.png) [@Tonkatsufan](https://www.ruby-forum.com/u/Tonkatsufan)\ **Post date:** [November 28, 2007, 10:46pm UTC](https://www.ruby-forum.com/t/nested-sets-from-database/121500/7 "2007-11-28T22:46:13Z")
On 28 Nov 2007, at 20:42, Tonkatsufan wrote: > Hi, > > thanks for this idea. I will try it tomorrow. > > I had this wishful thinking that it’s possible to somehow have a > “has\_many” relationship to the same table. Single-table inheritance > isn’t doing the trick. You can indeed have a self referential has\_many, eg class Node has\_many :children, :class\_name =\> ‘Node’, :foreign\_key =\> ‘parent\_id’ end Fred