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-/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
-~----------~----~----~----~------~----~------~--~---
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
[mailto:rubyonrails-talk@googlegroups.com] 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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" <parde...-go57ItdSaco@public.gmane.org> 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org [mailto:rubyonrails-talk@googlegroups.com] 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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
<ressis...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
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"
<parde...-go57ItdSaco@public.gmane.org> 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
[mailto:rubyonrails-talk@googlegroups.com] 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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
-----Original Message-----
From: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
[mailto:rubyonrails-talk@googlegroups.com] On Behalf Of ressister
Sent: Thursday, October 02, 2008 1:26 PM
To: Ruby on Rails: Talk
Subject: [Rails] Re: Grouping data for charting
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"
<parde...-go57ItdSaco@public.gmane.org> 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
> [mailto:rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org] 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---