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.
Michael M. (Guest)
on 2009-05-07 05: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.
Mark T. (Guest)
on 2009-05-07 15: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".
Michael M. (Guest)
on 2009-05-07 18:00
Mark T. 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\"}>]"
Michael M. (Guest)
on 2009-05-07 18: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
Rob B. (Guest)
on 2009-05-08 02: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 B.    http://agileconsultingllc.com
removed_email_address@domain.invalid
Michael M. (Guest)
on 2009-05-08 06: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.