Right now I am wasting a query and also losing the order of Users. Was
wondering what t he proper way to do this query is?
@top_tipper_ids = Tip.find_by_sql("SELECT SUM(tips.amount_cents)
total_tip, tips.client_id FROM tips WHERE tips.vendor_id = #{@user.id}
GROUP BY tips.client_id ORDER BY total_tip").collect {|e| e.client_id}
@top_tippers = User.find_all_by_id(@top_tipper_ids)
Trying to find the top tippers (Users) for a specific vendor.
--
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
Sprite,
I would propose a custom view that exploits SQL powers. I do not know how
much info you would like to syphon from the user model. Here is what I think
you can do:
*Code (In User model):
*
sql_query = "SELECT total_tippers.total_tips AS total_tips,
total_tippers.client_id AS client_id
users.first_name AS first_name,
users.last_name AS surname,
users.add_all_other_fields_you_require AS each_required_field
FROM (
SELECT SUM(tips.amount_cents)AS total_tips, tips.client_id AS client_id
FROM tips
WHERE tips.vendor_id = #{@user.id}
GROUP BY tips.client_id
ORDER BY total_tip ) AS top_tippers, users
WHERE top_tippers.client_id = users.id"
@top_tippers = self.find_by_sql(sql_query)
*Expected/Sample Output (tabulated for the sake of crarity):*
*total_tips client_id first_name surname each_required_field*
25 4 Aake Gregertsen data_1
23 1 Edmond Kachale data_1
*Points to note:*
- I chose to use *User* model. This is to reflect that the top_tipper is
a user not the tip itself. (for *sense* and *readability*''s sake).
In
addition, I think it''s a *user* who *has* (*many*) *tips* and not
the
other way round ([?]).
- If you only want to use the user (without making use of the other data
e.g. total_tips, client_id), as depicted by the "*.collect*"
operation on
your Tip model, then you can just scrap off the fields in the outer query so
that it appears like this: sql_query = "SELECT * FROM (
SELECT SUM(tips.amount_cents)AS total_tips, tips.client_id AS
client_id
FROM tips
WHERE tips.vendor_id = #{@user.id}
GROUP BY tips.client_id
ORDER BY total_tip ) AS top_tippers, users
WHERE top_tippers.client_id = users.id" . But be ready to hassle a bit
in order to find the fields you want. May be you can use script/console to
figure out, though most geeks discourage testing codes from script/console.
- *Caution*: I haven''t tested the code; *expect a bug if you use as
it is
*. You may need to customize it to fit your models and tables details.
(nice one, isn''t it? [?][?])
Sorry for *my long folk-tale*. [?][?]
Regards,
---
Edmond
Software Developer | Baobab Health Trust (http://www.baobabhealth.org/) |Malawi
Cell: +265 999 465 137 | +265 881 234 717
Skype: ceekays
*"A more radical argument for [statistical Natural Language Processing] is
that human cognition is probabilistic and that language must therefore be
probabilistic too since it is an integral part of cognition." -- Chris
Manning (1999)*, *Foundations of Statistical Natural Language Processing*.
2010/8/10 sprite <aake.gregertsen-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> Right now I am wasting a query and also losing the order of Users. Was
> wondering what t he proper way to do this query is?
>
> @top_tipper_ids = Tip.find_by_sql("SELECT SUM(tips.amount_cents)
> total_tip, tips.client_id FROM tips WHERE tips.vendor_id = #{@user.id}
> GROUP BY tips.client_id ORDER BY total_tip").collect {|e| e.client_id}
>
> @top_tippers = User.find_all_by_id(@top_tipper_ids)
>
> Trying to find the top tippers (Users) for a specific vendor.
>
> --
> 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
>
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org>
> .
> For more options, visit this group at
> http://groups.google.com/group/rubyonrails-talk?hl=en.
>
>
--
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
sprite wrote:> Right now I am wasting a query and also losing the order of Users. Was > wondering what t he proper way to do this query is? > > @top_tipper_ids = Tip.find_by_sql("SELECT SUM(tips.amount_cents) > total_tip, tips.client_id FROM tips WHERE tips.vendor_id = #{@user.id} > GROUP BY tips.client_id ORDER BY total_tip").collect {|e| e.client_id} > > @top_tippers = User.find_all_by_id(@top_tipper_ids) > > Trying to find the top tippers (Users) for a specific vendor.Will the Calculations module help you here? If not, you could add another join to get the user info in one query. And never -- but never -- interpolate the user ID in the string the way you''re doing. Use placeholders, or you leave yourself open for SQL injection. Best, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/. -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Thanks for leading me on the right path. Fixed a few errors and it
worked great:
def top_tippers
sql_query = "SELECT top_tippers.total_tips AS total_tips,
top_tippers.client_id AS client_id,
users.login_slug AS login_slug,
users.login AS login
FROM (
SELECT SUM(tips.amount_cents) AS total_tips,
tips.client_id AS client_id
FROM tips
WHERE tips.vendor_id = #{self.id}
GROUP BY tips.client_id
ORDER BY total_tips DESC) AS top_tippers, users
WHERE top_tippers.client_id = users.id"
@top_tippers = User.find_by_sql(sql_query)
end
What''s the danger of interpolating the id directly? It is not passed
in any way from the user. It is the primary_key integer ID of the
user. How do I use placeholders when constructing the query?
On Aug 10, 12:23 pm, Marnen Laibow-Koser
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:> sprite wrote:
> > Right now I am wasting a query and also losing the order of Users. Was
> > wondering what t he proper way to do this query is?
>
> > @top_tipper_ids = Tip.find_by_sql("SELECT SUM(tips.amount_cents)
> > total_tip, tips.client_id FROM tips WHERE tips.vendor_id =
#...@user.id}
> > GROUP BY tips.client_id ORDER BY total_tip").collect {|e|
e.client_id}
>
> > @top_tippers = User.find_all_by_id(@top_tipper_ids)
>
> > Trying to find the top tippers (Users) for a specific vendor.
>
> Will the Calculations module help you here?
>
> If not, you could add another join to get the user info in one query.
>
> And never -- but never -- interpolate the user ID in the string the way
> you''re doing. Use placeholders, or you leave yourself open for
SQL
> injection.
>
> Best,
> --
> Marnen Laibow-Koserhttp://www.marnen.org
> mar...-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
> --
> Posted viahttp://www.ruby-forum.com/.
--
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@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.
sprite wrote:> Thanks for leading me on the right path.Whom are you addressing? Please quote when replying in future.> Fixed a few errors and it > worked great: > > def top_tippers > sql_query = "SELECT top_tippers.total_tips AS total_tips, > top_tippers.client_id AS client_id, > users.login_slug AS login_slug, > users.login AS login > FROM ( > SELECT SUM(tips.amount_cents) AS total_tips, > tips.client_id AS client_id > FROM tips > WHERE tips.vendor_id = #{self.id} > GROUP BY tips.client_id > ORDER BY total_tips DESC) AS top_tippers, users > WHERE top_tippers.client_id = users.id" > @top_tippers = User.find_by_sql(sql_query) > end >You probably don''t need to write that much SQL. Again, see if the Calculations module will help.> > What''s the danger of interpolating the id directly? It is not passed > in any way from the user. It is the primary_key integer ID of the > user.How sure can you be that you won''t get passed a bogus ID?> How do I use placeholders when constructing the query?Read the find_by_sql documentation. Best, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/. -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Sprite,
I propose a small change. Add a space between the sql_query string and the
@top_tippers variable (for readability sake):
def top_tippers
sql_query = "SELECT top_tippers.total_tips AS total_tips,
top_tippers.client_id AS client_id,
users.login_slug AS login_slug,
users.login AS login
FROM (
SELECT SUM(tips.amount_cents) AS total_tips,
tips.client_id AS client_id
FROM tips
WHERE tips.vendor_id = #{self.id}
GROUP BY tips.client_id
ORDER BY total_tips DESC) AS top_tippers, users
WHERE top_tippers.client_id = users.id"
@top_tippers = User.find_by_sql(sql_query)
end
And this blog post may be helpful too as you code: Top 10 Things That Annoy
Programmers<http://www.kevinwilliampang.com/2008/08/28/top-10-things-that-annoy-programmers/>
.
Regards,
---
Edmond
Software Developer | Baobab Health Trust (http://www.baobabhealth.org/) |
Malawi
Cell: +265 999 465 137 | +265 881 234 717
*"Many people doubt open source software and probably don’t realize that
there is an alternative… which is just as good.." -- Kevin Scannell*
--
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@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.