Nested sets from database

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

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

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).

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

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.

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

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 :wink:

Anyway - thank you for the idea

Tonkatsu

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:
http://db4free.blogspot.com/2006/01/hail-to-groupconcat.html

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

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