Jump to content

The ultimate community for Ruby on Rails developers.


Photo

Multiple SQL calls?


  • Please log in to reply
9 replies to this topic

#1 Rowel

Rowel

    Controller

  • Members
  • 109 posts

Posted 02 September 2013 - 04:07 AM

I've been playing with Rails Console, trying to get familiar with associations, etc... and have a question.

 

I have 2 tables/model, User and Post

user has_many posts

post belongs_to user

 

In the console, let's say my 

user.id= 22 

There are (3) records in posts matching user_id = 22.  

 

I want to delete all posts belonging to user_id=22,  I do a 

user.posts.delete_all   

Okay, so It works... all (3) posts records with user_id = 22 are deleted.  

 

But I see in the Rails console, Rails has to execute 3 separate SQL commands, once for each post.id  (in this case, posts.id# 31, 32, 33) 

 

Is this "normal" with Rails? Seems rather inefficient. 

   (0.1ms)  BEGIN
  SQL (0.1ms)  DELETE FROM "posts" WHERE "posts"."id" = $1  [["id", 31]]
  SQL (0.1ms)  DELETE FROM "posts" WHERE "posts"."id" = $1  [["id", 32]]
  SQL (0.1ms)  DELETE FROM "posts" WHERE "posts"."id" = $1  [["id", 33]]
   (0.4ms)  COMMIT

I'm just imagining, what if there are thousands of records in posts? Rails will execute also a thousand distinct DELETE commands?  

 

Is there way to make Rails execute just a single SQL without resorting to writing custom SQL in the Rails application? 

 

I want Rails to execute instead

DELETE FROM "posts" WHERE "user_id" = 22 


#2 Funnyvibe

Funnyvibe

    Signalman

  • Members
  • 17 posts

Posted 02 September 2013 - 04:41 AM

That's because it's returning an array of active record objects and you are calling the delete method on each of these items individually. The destroy_all(condition) method may be what you are looking for but I have not looked into it myself.

#3 Rowel

Rowel

    Controller

  • Members
  • 109 posts

Posted 02 September 2013 - 04:45 AM

Hi, same result with destroy_all... multiple SQL calls are also generated. 

2.0.0p247 :072 > user.posts.destroy_all
  Post Load (0.3ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = $1  [["user_id", 20]]
   (0.1ms)  BEGIN
  SQL (0.2ms)  DELETE FROM "posts" WHERE "posts"."id" = $1  [["id", 42]]
  SQL (0.1ms)  DELETE FROM "posts" WHERE "posts"."id" = $1  [["id", 43]]
  SQL (0.1ms)  DELETE FROM "posts" WHERE "posts"."id" = $1  [["id", 44]]
   (0.4ms)  COMMIT


#4 Rowel

Rowel

    Controller

  • Members
  • 109 posts

Posted 02 September 2013 - 04:50 AM

The alternative is execute a custom SQL command, but isn't that against the "Rails way" of doing things?

Posts.connection.execute("delete from posts where user_id=20")


#5 Funnyvibe

Funnyvibe

    Signalman

  • Members
  • 17 posts

Posted 02 September 2013 - 04:58 AM

You may want to to write your own delete all method for your model in that case that deals with the array and creates it's own query. That's what the other methods do anyway at a much lower level, you just don't see it happening. I'll look around to see if there is a more "proper" way, but it isn't out of the ordinary to have to write some queries for the sake of efficiency in some situations.

#6 Ohm

Ohm

    Guard

  • Members
  • 179 posts
  • LocationCopenhagen

Posted 02 September 2013 - 05:18 AM

What is you eager load the posts?

User.includes(:posts).posts.destroy_all

Blog: http://ohm.sh | Twitter: madsohm


#7 Rowel

Rowel

    Controller

  • Members
  • 109 posts

Posted 02 September 2013 - 07:40 AM

BINGO!

 

Ohm, thanks for your post. That pointed me in the right direction -- with some corrections. 

User.includes(:posts).posts.destroy_all

The above command produces the correct SQL statement shown below, but raises an ActiveRecord::Configuration error.  

SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = $1  [["user_id", 24]]
ActiveRecord::ConfigurationError: Association named 'post' was not found; perhaps you misspelled it?

.....

 

so I tried another approach. 

 

Instead of using destroy_all, I used delete_all and re-arranged where incudes( ) should be in the sequence. and I used :post, instead of :posts

 

 

The following command works perfectly! 

> user.posts.includes(:post).delete_all
  SQL (0.7ms)  DELETE FROM "posts" WHERE "posts"."user_id" = $1  [["user_id", 24]]
 => 4
 

Only (1) SQL command is generated, and no ActiveRecord errors, and all posts records are deleted!  

 

Can someone explain what .includes(:post) exactly do?  

 

Thanks all! 



#8 Rowel

Rowel

    Controller

  • Members
  • 109 posts

Posted 02 September 2013 - 08:04 PM

More interesting findings.... 

 

1.  If I use destroy_all,  even with .includes( ),  individual records are deleted individually resulting in multiple SQL commands.  

I guess because it tries to call a callback for each record? 

 

2. If I use delete_all,  without .includes, it also generates multiple SQL commands.  (even if it doesn't do any callbacks)

 

So the trick is to use .includes.

But it seems even an empty string for includes( ) works fine. The correct SQL command is generated. 

 user.posts.includes('').delete_all
  SQL (0.7ms)  DELETE FROM "posts" WHERE "posts"."user_id" = $1  [["user_id", 24]]


#9 Funnyvibe

Funnyvibe

    Signalman

  • Members
  • 17 posts

Posted 02 September 2013 - 08:18 PM

I looked into it a bit and delete_all is intended to remove records in one single statement like I had thought. I would try Post.delete_all(:user_id => user.id) and see if that accomplishes what you need.

#10 Rowel

Rowel

    Controller

  • Members
  • 109 posts

Posted 03 September 2013 - 12:32 PM

I would try Post.delete_all(:user_id => user.id) 

 

Yeah, that works too, but the association with user model is gone.  

 

My question is does keeping the association with 'user' important?  i.e. user.posts    vs  Post 

Just a newbie asking question on what's the best practice. 

 

Post.delete_all(user_id: user.id)  

user.posts.delete_all        <-- generates multiple SQL delete 

user.posts.includes('').delete_all       <---- single SQL delete 






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users