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 <[email protected]> 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 = #[email protected]}
> > 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
> [email protected]
> --
> 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 [email protected].
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.
