On 6 mai, 22:29, Jeremy Evans <[email protected]> wrote:
> Updating joined datasets is not standard SQL and Sequel doesn't
> currently support it.
so this would be an specific adapter feature ?
> 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
so many comments and posts make it slow
> 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)})
I am using postgresql and it supports it.
>
> 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.
That's a great idea, the trigger will be difficult to write but it's
definitly a good option.
Thanks for your help Jeremy !
Cheers,
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
-~----------~----~----~----~------~----~------~--~---
