Grouping data for charting

rubyonrails-talk
1

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

2

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

3

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

4

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

5

Ah, right.

Well, that SQL will only ever return a single row, so you should be able to reference the single element contained therein, like so:

@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)')[0]

HTH,

-Roy