On May 6, 10:53 am, Florent <[email protected]> wrote:
> I'am trying to update a complex dataset using join.
> I don't think it's a sequel issue but more the way I try to build my
> query:
>
> I have 2 models:
> - Post: one_to_many :comments
> - Comment: many_to_one :post
>
> I would like to retrieve a subset of comments and to update all the
> posts linked to them.
>
> First, l tried this:
> junk_comments = Comment.filter("is_junk = true").select(:comments__id)
> Post.where(:id => Post.join(Comment, :post_id => :id).where
> (:comments__id => junk_comments).select(:posts__id)).update(:junk
> => :junk + 1)
>
> but with this query, if I have only one post updated for many filtered
> comments of that post
>
> So I tried to join or eager_graph:
> Post.join(Comment, :post_id => :id, :comments__id =>
> junk_comments).update(:junk => :junk + 1)
>
> but this error occurs: Sequel::InvalidOperation: A joined dataset
> cannot be updated
>
> Even if I select(:posts.*) I can't make it work.
Updating joined datasets is not standard SQL and Sequel doesn't
currently support it.
This is fairly easy to do in multiple queries:
Post.join(Comment, :post_id
=> :id, :id=>junk_comments).group_and_count(:posts__id).all do |r|
Post.filter(r[:id]=>)update(:junk => :junk + r[:count])
end
To do it in a single query you need to do a subselect. I'm not sure
if the database you are using supports it, but the syntax would be
something like:
Post.filter(:id=>Comment.select(:post_id).filter
(:id=>junk_comments)).update(:junk=>:junk+Comment.filter
(:post_id=>:posts__id, :comments__id=>junk_comments).select{count
('*'.lit)})
>From what you are trying to do you are probably better off using a
database trigger that automatically increments/decrements the related
post's junk field when the comment's is_junk field is modified.
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
-~----------~----~----~----~------~----~------~--~---
