More Rails-ish Way?

Rails
1

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

2

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.

3

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

4

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 [email protected]:

5

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

Quoting Jeffrey L. Taylor [email protected]: