On Mar 27, 9:58 pm, Florent <[email protected]> wrote:
> 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
That error is specific to Sequel's PostgreSQL support. It's because
when you update a table in PostgreSQL when joining, you need to have a
table for the FROM clause:
UPDATE table1 SET ... FROM table2 JOIN ... WHERE ...
Now, it is theoretically possible to work without a second FROM table
and use only JOINs by transforming the first JOIN into a FROM table
and a WHERE clause. However, nobody has written code to do that, and
I'm not sure it's possible to get the correct semantics for LEFT/RIGHT/
FULL joins.
The current support is not a major issue anyway, as updating a JOINed
dataset is database specific, and restructing your query use multiple
FROM tables is generally straightforward.
> 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
I don't think you actually need anything special for this. If only a
subselect is needed, why not just:
User.filter(:id=>inactive).update(:favorites => :favorites - 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 ?
Ah, that's why you are doing the join. I'd just do a separate
subquery to get the number of affected rows:
User.filter(:id=>inactive).update(:favorites => :favorites -
DB[:albums].select{count(user_id)}.
filter{updated_at < Time.now}.
filter(:user_id=>:users__id))
You might want to modify that to better handle NULLs.
You'd have to ask on the PostgreSQL mailing list about why your JOIN
doesn't work. I'm not sure if it's because of an error in the code or
simply how PostgreSQL handles the updating of JOINed datasets.
Jeremy
--
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.
