Forum: Ruby How to break this down for use in a graph?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
5565e460a42fbe5669b458b2753f8d34?d=identicon&s=25 Michael Modic (codeslush)
on 2009-05-07 03:15
Hi,

I have been trying for many hours to figure out how to do this, and have
yet to come up with a solution.  It's my lack of experience I'm sure and
I hope someone can point me in the right direction.

I have an ActiveRecord resultset that contains rows from a query with
group by clauses.  I'm trying to generate a stacked column graph that
will show the number of orders for a period of time stacked by the rep
that generated the orders broken down by date.

The AR result set has the following attributes:

sale_date (date the sale was made - for a single date, there will be a
row for each each rep that had a recorded sale on that date)
sales_rep (the rep - the name of the rep that made the sale on the sale
date)
order_count (the count of orders made by the above rep for the sale
date)

The graph expects an array of dates for the axis.  I did:

  sale_days = @results.map{ |item | item.sales_day }.uniq

to get a unique set of dates (as they repeat for each rep, but I only
want the unique dates).

I don't have a clue how to get something that allows me to do this:

   sales_graph.add( :series, "Sales Rep Name", [a, b, c, d, e] )

where the sales rep name is found in my result set, and the a,b,c... is
the actual count of orders for that rep for each day.  I need to do the
sales_graph.add... for each rep found in the resultset. I can handle
that part, if I can figure out how to get the counts for a rep into it's
own array/hash and tied back to the rep.

Please help!  :-(  If I need to structure my query differently, I can.
Ruby is so beautiful and I know I am missing some easy way to do this
and for the life of me I can't figure it out on my own.

Thanks for any pointers in advance,

M.
134ea397777886d6f0aa992672a50eaa?d=identicon&s=25 Mark Thomas (Guest)
on 2009-05-07 13:18
(Received via mailing list)
> Please help!  :-(  If I need to structure my query differently, I can.
> Ruby is so beautiful and I know I am missing some easy way to do this
> and for the life of me I can't figure it out on my own.

It would help if you show your model or at least 'p @results'. Then
show what you want as output. For me at least, code will be easier to
decipher than what you mean by things like "tied back to the rep".
5565e460a42fbe5669b458b2753f8d34?d=identicon&s=25 Michael Modic (codeslush)
on 2009-05-07 16:00
Mark Thomas wrote:
>> Please help! �:-( �If I need to structure my query differently, I can.
>> Ruby is so beautiful and I know I am missing some easy way to do this
>> and for the life of me I can't figure it out on my own.
>
> It would help if you show your model or at least 'p @results'. Then
> show what you want as output. For me at least, code will be easier to
> decipher than what you mean by things like "tied back to the rep".

Sorry about that.  Here's a sample of the results after calling
.inspect.  Should help clarify the structure of my resultset.

#<Client:0x4920884 @attributes={\"order_count\"=>\"4\",
\"sales_day\"=>\"2009-04-13\", \"sales_rep\"=>\"Michael\"}>,
#<Client:0x4920848 @attributes={\"order_count\"=>\"2\",
\"sales_day\"=>\"2009-04-13\", \"sales_rep\"=>\"John\"}>,
#<Client:0x492080c @attributes={\"order_count\"=>\"10\",
\"sales_day\"=>\"2009-04-13\", \"sales_rep\"=>\"Jane\"}>,
#<Client:0x49204c4 @attributes={\"order_count\"=>\"1\",
\"sales_day\"=>\"2009-04-14\", \"sales_rep\"=>\"Michael\"}>,
#<Client:0x4920488 @attributes={\"order_count\"=>\"9\",
\"sales_day\"=>\"2009-04-14\", \"sales_rep\"=>\"John\"}>,
#<Client:0x4920438 @attributes={\"order_count\"=>\"7\",
\"sales_day\"=>\"2009-04-14\", \"sales_rep\"=>\"David\"}>,
#<Client:0x49203e8 @attributes={\"order_count\"=>\"2\",
\"sales_day\"=>\"2009-04-14\", \"sales_rep\"=>\"Albert\"}>]"
5565e460a42fbe5669b458b2753f8d34?d=identicon&s=25 Michael Modic (codeslush)
on 2009-05-07 16:52
> #<Client:0x4920884 @attributes={\"order_count\"=>\"4\",
> \"sales_day\"=>\"2009-04-13\", \"sales_rep\"=>\"Michael\"}>,
> #<Client:0x4920848 @attributes={\"order_count\"=>\"2\",
> \"sales_day\"=>\"2009-04-13\", \"sales_rep\"=>\"John\"}>,
> #<Client:0x492080c @attributes={\"order_count\"=>\"10\",
> \"sales_day\"=>\"2009-04-13\", \"sales_rep\"=>\"Jane\"}>,
> #<Client:0x49204c4 @attributes={\"order_count\"=>\"1\",
> \"sales_day\"=>\"2009-04-14\", \"sales_rep\"=>\"Michael\"}>,
> #<Client:0x4920488 @attributes={\"order_count\"=>\"9\",
> \"sales_day\"=>\"2009-04-14\", \"sales_rep\"=>\"John\"}>,
> #<Client:0x4920438 @attributes={\"order_count\"=>\"7\",
> \"sales_day\"=>\"2009-04-14\", \"sales_rep\"=>\"David\"}>,
> #<Client:0x49203e8 @attributes={\"order_count\"=>\"2\",
> \"sales_day\"=>\"2009-04-14\", \"sales_rep\"=>\"Albert\"}>]"

I forgot to put what I wanted for output.  With the results above as a
sample, I want:

sales_dates = [2009-04-13, 2009-04-14]

Then...

Michael, [4, 1]
John, [2, 9]
Jane, [10, 0]
David, [0, 7]
Albert, [0, 2]

or whatever it would take for me to iterate over an array/hash of
sales_reps and call something like the following:

for rep in sales_reps
  sales_graph.add( :series, rep, [a, b] )
end

so that I would end up with:
  sales_graph.add( :series, "Michael", [4, 1] )
  sales_graph.add( :series, "John", [2, 9] )
  sales_graph.add( :series, "Jane", [10, 0] )
  sales_graph.add( :series, "David", [0, 7] )
  sales_graph.add( :series, "Albert", [0, 2] )

I hope this helps.  Again, any advice is greatly appreciated.

Regards,

Michael
Ef3aa7f7e577ea8cd620462724ddf73b?d=identicon&s=25 Rob Biedenharn (Guest)
on 2009-05-08 00:39
(Received via mailing list)
On May 7, 2009, at 10:53 AM, Michael Modic wrote:

>> #<Client:0x4920438 @attributes={\"order_count\"=>\"7\",
>> \"sales_day\"=>\"2009-04-14\", \"sales_rep\"=>\"David\"}>,
>> #<Client:0x49203e8 @attributes={\"order_count\"=>\"2\",
>> \"sales_day\"=>\"2009-04-14\", \"sales_rep\"=>\"Albert\"}>]"
>
> I forgot to put what I wanted for output.  With the results above as a
> sample, I want:

OK, so I'll start with @results having AR model instances like the
above.

# First, let's make it into a simpler data structure:
# a hash with keys of the sales_rep with values as an array of
# [sales_day, order_count] pairs.
sales_data = Hash.new {|h,k| h[k] = []}
@results.each do |client|
   sales_data[client.sales_rep] << [client.sales_day,
client.order_count]
end

> sales_dates = [2009-04-13, 2009-04-14]

# get the unique, sorted set of dates
sales_dates = sales_data.values.map{|pairs|pairs.map{|pair|
pair[0]}}.flatten.uniq.sort

# Perhaps you want to eliminate "holes" in the array of sales_dates

# Make sure each sales_rep has the same set of entries
sales_data.keys.each do |rep|
   sales_data[rep] = sales_dates.map do |d|
     if pair = sales_data[rep].assoc(d)
       pair[1].to_i
     else
       0
     end
   end
end

>
>
> Then...
>
> Michael, [4, 1]
> John, [2, 9]
> Jane, [10, 0]
> David, [0, 7]
> Albert, [0, 2]

irb> pp sales_data
{"Michael"=>[4, 1],
  "David"=>[0, 7],
  "John"=>[2, 9],
  "Albert"=>[0, 2],
  "Jane"=>[10, 0]}
=> nil

>  sales_graph.add( :series, "Michael", [4, 1] )
>
> --

sales_data.each do |rep,sales|
   sales_graph.add(:series, rep, sales)
end

Assuming that you have sales_graph defined somewhere up front.

-Rob

Rob Biedenharn    http://agileconsultingllc.com
Rob@AgileConsultingLLC.com
5565e460a42fbe5669b458b2753f8d34?d=identicon&s=25 Michael Modic (codeslush)
on 2009-05-08 04:59
Rob - thank you so much!!!  I'll email you privately to make a small
contribution for your assistance.  It seems so easy now after seeing the
solution, but I would not have got it on my own.

Regards,

Michael
This topic is locked and can not be replied to.