It's a bit ugly, but here's what i did:
@entries = Entry.find_by_sql ["SELECT sum(entries.price) AS sum_price,
categories.name AS category_name, categories.color AS category_color
FROM entries, categories, entry_categories
WHERE entries.user_id = ? AND entries.id =
entry_categories.entry_id and entry_categories.category_id =
categories.id
GROUP BY categories.id", current_user.id]
uncategorized = Entry.find_by_sql('select sum(entries.price) as
sum_price, "uncategorized" as category_name, "#CCCCCC" as
category_color from entries where id not in (select entry_id from
entry_categories)')
for uncat_entry in uncategorized
@entries << uncat_entry
end
Thanks again for your help. If you have another suggestion, let me
know!
-A
On Oct 2, 4:26 pm, ressister <[EMAIL PROTECTED]> wrote:
> Great suggestion Roy, thanks! I even added category_color for
> uncategorized entries in the SQL query you built. Take a look at the
> output though...
>
> [#<Entry >, #<Entry >, #<Entry >, #<Entry >, [#<Entry >]]
>
> The 5th entry here is the "uncategorized" entry. It's in brackets
> which is tripping up Open Flash Chart. How do i remove that extra set
> of brackets?
>
> -A
>
> On Oct 2, 3:08 pm, "Pardee, Roy" <[EMAIL PROTECTED]> wrote:
>
> > How about supplementing what you're already getting back w/a call like this:
>
> > @entries << Entry.find_by_sql('select sum(entries.price) as sum_price,
> > "uncategorized" as category_name from entries where id not in (select
> > entry_id from entry_categories)')
>
> > HTH,
>
> > -Roy
>
> > -----Original Message-----
> > From: rubyonrails-talk@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf
> > Of ressister
> > Sent: Thursday, October 02, 2008 11:58 AM
> > To: Ruby on Rails: Talk
> > Subject: [Rails] Grouping data for charting
>
> > Hi there, I'm using Open Flash Chart to create a pie chart of user's
> > categorized spending. It currently includes only categories that I have in
> > my Category model, but does not include "uncategorized"
> > entries which have no association in the entry_categories table.
>
> > I'd like to include those entries that are not categorized as well in my
> > grouping so that I can display them in the pie chart as "uncategorized."
> > How would i do that? Below is the SQL that successfully grabs categorized
> > entries. How would i retrieve and group uncategorized entries as part of
> > this query?
>
> > @entries = Entry.find_by_sql ["SELECT sum(entries.price) AS sum_price,
> > categories.name AS category_name, categories.color AS category_color
> > FROM entries, categories, entry_categories
> > WHERE entries.user_id = ? AND entries.id = entry_categories.entry_id
> > and entry_categories.category_id = categories.id
> > GROUP BY categories.id", current_user.id]
>
> > Thanks in advance.
> > -A
--~--~---------~--~----~------------~-------~--~----~
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@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
