# How do I optimize this query?

**URL:** <https://www.ruby-forum.com/t/how-do-i-optimize-this-query/193042>\
**Category:** Rails\
**Created:** [August 10, 2010, 12:37pm UTC](https://www.ruby-forum.com/t/how-do-i-optimize-this-query/193042 "2010-08-10T12:37:40Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![sprite](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/a4c791/32.png) [@sprite](https://www.ruby-forum.com/u/sprite)\
**Post date:** [August 10, 2010, 12:37pm UTC](https://www.ruby-forum.com/t/how-do-i-optimize-this-query/193042/1 "2010-08-10T12:37:40Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![sprite](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/a4c791/32.png) [@sprite](https://www.ruby-forum.com/u/sprite)\
**Post date:** [August 10, 2010, 5:35pm UTC](https://www.ruby-forum.com/t/how-do-i-optimize-this-query/193042/2 "2010-08-10T17:35:45Z")

</div>

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/](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 [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![sprite](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/a4c791/32.png) [@sprite](https://www.ruby-forum.com/u/sprite)\
**Post date:** [August 10, 2010, 6:23pm UTC](https://www.ruby-forum.com/t/how-do-i-optimize-this-query/193042/3 "2010-08-10T18:23:41Z")

</div>

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](http://www.marnen.org)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![sprite](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/a4c791/32.png) [@sprite](https://www.ruby-forum.com/u/sprite)\
**Post date:** [August 10, 2010, 9:24pm UTC](https://www.ruby-forum.com/t/how-do-i-optimize-this-query/193042/4 "2010-08-10T21:24:07Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![sprite](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/a4c791/32.png) [@sprite](https://www.ruby-forum.com/u/sprite)\
**Post date:** [August 10, 2010, 9:49pm UTC](https://www.ruby-forum.com/t/how-do-i-optimize-this-query/193042/5 "2010-08-10T21:49:18Z")

</div>

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](http://www.marnen.org)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![sprite](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/a4c791/32.png) [@sprite](https://www.ruby-forum.com/u/sprite)\
**Post date:** [August 11, 2010, 10:27am UTC](https://www.ruby-forum.com/t/how-do-i-optimize-this-query/193042/6 "2010-08-11T10:27:54Z")

</div>

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/](http://www.kevinwilliampang.com/2008/08/28/top-10-things-that-annoy-programmers/)  
.

Regards,

* * *

Edmond  
Software Developer | Baobab Health Trust ([http://www.baobabhealth.org/](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_