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