Hi Sequel users,
I got some troubles with my dataset, here is my setup :
2 tables : users and albums, where users can have many albums (one
column user_id in albums)
I would like to update a value in a column of users table depending a
sub-query on albums table. So I can have many rows in this sub-query
related to the same user.
So like I can't do this using a basic JOIN : "Sequel::Error: Need
multiple FROM tables if updating/deleting a dataset with JOINs", I
followed the error's advice and tried :
User.from(:users, :albums).filter(:users__id =>
inactive).update(:favorites => :favorites - 1)
UPDATE "users" SET "favorites" = ("favorites" - 1) FROM "albums" WHERE
("users"."id" IN (SELECT "user_id" FROM "albums" WHERE
(albums.updated_at < '2011-03-28 00:32:08.414057-0400')))
=> 1
but this returns only 1 affected row although without update method it
returns 3 !
User.from(:users, :albums).filter(:users__id => inactive).count
SELECT COUNT(*) AS "count" FROM "users", "albums" WHERE ("users"."id"
IN (SELECT "user_id" FROM "albums" WHERE (albums.updated_at <
'2011-03-28 00:32:08.414057-0400'))) LIMIT 1
=> 3
I know it's because it's the same user, but I would like to update the
user as many times he's included in the sub-query, not just once. The
dataset includes the 3 same user though, but it appears that the
update method doesn't use the "from" argument, it doesn't generate the
good SQL query or am I wrong ?
thanks for your help
Florent
--
You received this message because you are subscribed to the Google Groups
"sequel-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/sequel-talk?hl=en.
