Thanks for the hint Jeremy, I will try that. I will also try to find why my join query doesn't work.
keep you posted. Florent On Mar 28, 12:51 pm, Jeremy Evans <[email protected]> wrote: > 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.
