On Tuesday, October 31, 2017 at 7:16:44 PM UTC+1, Lincoln Hawk wrote: > > Thank you very much Jeremy. > > I changed to this: > > @db[:threads] > .where(:user_id => 9) > .exclude(:id => @db[:posts].where(:user_id => 9).select(:thread_id)) > .delete() > > And it runs as fast as raw SQL. > > Just a feedback, for my first version without distinct(), it was still > very slow. From the principle of least astonishment > <https://en.wikipedia.org/wiki/Principle_of_least_astonishment>, I think > it should work like the raw SQL. >
Your first version without distinct and this code result in exactly the same SQL here modulo the order of the WHERE clause, so they should perform the same: @db[:threads] .where(:user_id => 9) .exclude(:id => @db[:posts].where(:user_id => 9).select(:thread_id)) .delete() # DELETE FROM threads WHERE ((user_id = 9) AND (id NOT IN (SELECT thread_id FROM posts WHERE (user_id = 9)))) @db[:threads] .where(:id => @db[:posts].where(:user_id => 9).select(:thread_id)) .invert() .where(:user_id => 9) .delete() # DELETE FROM threads WHERE ((id NOT IN (SELECT thread_id FROM posts WHERE (user_id = 9))) AND (user_id = 9)) Thanks, Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
