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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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).
On Nov 28, 9:00 am, Tonkatsufan
<tonkatsu...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> 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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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 On Nov 28, 5:54 pm, AndyV <a...-HmMyXyqgL2CVc3sceRu5cw@public.gmane.org> wrote:> 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). > > On Nov 28, 9:00 am, Tonkatsufan <tonkatsu...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > 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--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Nov 28, 5:08 pm, "MrBana...-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org" <MrBana...-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> 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.> 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... > endYes, 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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> Cheers > Tonkatsu > > On Nov 28, 5:54 pm, AndyV <a...-HmMyXyqgL2CVc3sceRu5cw@public.gmane.org> wrote: >> 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). >> >> On Nov 28, 9:00 am, Tonkatsufan <tonkatsu...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> >>> 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 > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---