# More Rails-ish Way?

**URL:** <https://www.ruby-forum.com/t/more-rails-ish-way/143950>\
**Category:** Rails\
**Created:** [July 24, 2008, 3:18am UTC](https://www.ruby-forum.com/t/more-rails-ish-way/143950 "2008-07-24T03:18:42Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jeffrey\_LSTaylor](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ee7513/32.png) [@Jeffrey\_LSTaylor](https://www.ruby-forum.com/u/Jeffrey_LSTaylor)\
**Post date:** [July 24, 2008, 3:18am UTC](https://www.ruby-forum.com/t/more-rails-ish-way/143950/1 "2008-07-24T03:18:42Z")

</div>

I need to to an update with a a join. Is there a better way than this?

Token.connection.update “UPDATE tokens INNER JOIN items\_tokens ON  
tokens.id =  
items\_tokens.token\_id SET field=field-1 WHERE items\_tokens.item\_id =  
#{item.id}”

There are possibly hundreds of tokens for an item (actually words and  
word  
pairs in RSS feed descriptions), so instantiating a couple of hundred AR  
for  
each click is too CPU intensive.

TIA,  
Jeffrey

---

<div class="post-metadata">

**Author:** ![Jeffrey\_LSTaylor](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ee7513/32.png) [@Jeffrey\_LSTaylor](https://www.ruby-forum.com/u/Jeffrey_LSTaylor)\
**Post date:** [July 24, 2008, 7:48am UTC](https://www.ruby-forum.com/t/more-rails-ish-way/143950/2 "2008-07-24T07:48:09Z")

</div>

Jeffrey L. Taylor wrote:

> I need to to an update with a a join. Is there a better way than this?
> 
> Token.connection.update “UPDATE tokens INNER JOIN items\_tokens ON  
> tokens.id =  
> items\_tokens.token\_id SET field=field-1 WHERE items\_tokens.item\_id =  
> #{item.id}”
> 
> There are possibly hundreds of tokens for an item (actually words and  
> word  
> pairs in RSS feed descriptions), so instantiating a couple of hundred AR  
> for  
> each click is too CPU intensive.
> 
> TIA,  
> Jeffrey

There’s ar extensions but it appears to not even work with rails 2.

---

<div class="post-metadata">

**Author:** ![Jeffrey\_LSTaylor](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ee7513/32.png) [@Jeffrey\_LSTaylor](https://www.ruby-forum.com/u/Jeffrey_LSTaylor)\
**Post date:** [July 24, 2008, 7:10pm UTC](https://www.ruby-forum.com/t/more-rails-ish-way/143950/3 "2008-07-24T19:10:16Z")

</div>

Could you do something like:

Token.update\_all(‘field = field-1’, ‘id in (select token\_id from  
items\_tokens where item\_id = #{item.id})’)

?

I’m not sure where that “field-1” value is coming from. If it’s  
items\_tokens then you probably can’t…

As an aside, I wonder if it would be feasible to have has\_many add an  
update\_all method on the collection? So you could write code like:

my\_token.items.update\_all(“field = ‘new-value’”)

And have it generate the SQL for you? It seems like it should be  
possible… (And I don’t think AR already does that.)

-Roy

---

<div class="post-metadata">

**Author:** ![Jeffrey\_LSTaylor](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ee7513/32.png) [@Jeffrey\_LSTaylor](https://www.ruby-forum.com/u/Jeffrey_LSTaylor)\
**Post date:** [July 24, 2008, 8:46pm UTC](https://www.ruby-forum.com/t/more-rails-ish-way/143950/4 "2008-07-24T20:46:31Z")

</div>

Thank you. Haven’t double checked that the semantics are correct, but  
the  
performance is unacceptable. I’ll stick with the correct code, ugly as  
it  
is. Interesting that there is such a huge difference.

Jeffrey

mysql\> UPDATE tokens SET clicks = clicks+1 WHERE (id in (select token\_id  
from items\_tokens where item\_id = 143456));  
Query OK, 5 rows affected (7.58 sec)  
Rows matched: 5 Changed: 5 Warnings: 0

mysql\> UPDATE tokens INNER JOIN items\_tokens ON tokens.id =  
items\_tokens.token\_id SET clicks=clicks+1 WHERE items\_tokens.item\_id =  
143456;  
Query OK, 5 rows affected (0.10 sec)  
Rows matched: 5 Changed: 5 Warnings: 0

Quoting Pardee, Roy [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):

---

<div class="post-metadata">

**Author:** ![Jeffrey\_LSTaylor](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ee7513/32.png) [@Jeffrey\_LSTaylor](https://www.ruby-forum.com/u/Jeffrey_LSTaylor)\
**Post date:** [July 25, 2008, 4:23am UTC](https://www.ruby-forum.com/t/more-rails-ish-way/143950/5 "2008-07-25T04:23:35Z")

</div>

Oops, change ‘correct code’ to ‘current code’. AFAICT, both are  
correct.

Quoting Jeffrey L. Taylor [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):