More Rails-ish Way?

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

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.

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

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

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

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 <pardee.r@ghc.org>:

Oops, change 'correct code' to 'current code'. AFAICT, both are correct.

Quoting Jeffrey L. Taylor <ror@abluz.dyndns.org>: