Mysql to Rails SQL query

Question

How would I recreate the following query in Rails speak so that it returns the amount of rows processed?

self.connection.execute('DELETE FROM `cd_artist`
LEFT JOIN `cdpedia` ON cd_artist.id = cdpedia.`artistId`
WHERE artistId IS NULL;')

I realize I should use something like Artist.find_by_sql for it to return something, but that gives me an error.

Perhaps there is a more elegant way of doing this?

I want to avoid the Rails way of first searching the database, then grabbing the IDs and deleting the entries with Object.destroy or even Object.delete(id) since a SQL call is so much faster (if you have the right indexes), but I'm curious as to what the "proper" way to do it is.

Your query seems to be wrong. Have you already tested it directly on your database? — basgys
– basgys, Commented Jan 13, 2012 at 13:13

basgys · Accepted Answer · 2012-01-13 12:27:07Z

3

You just have to replace execute by delete, and it will return the number of rows affected.

self.connection.delete('DELETE FROM `cd_artist`
LEFT JOIN `cdpedia` ON cd_artist.id = cdpedia.`artistId`
WHERE artistId IS NULL;')

There is maybe a more elegant way to do this with ActiveRecord, but I doubt it will be as efficient as a raw SQL query.

edited Jan 13, 2012 at 12:27

answered Jan 13, 2012 at 12:20

basgys

4,39331 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kakubei Over a year ago

Thanks. So just use delete and also include the DELETE inside the query? I'm guessing connection.delete simply returns the number of rows affected (regardless of the actual query) and execute does not?

basgys Over a year ago

@kakubei Yes you have to include the keyword DELETE, because the query won't be analyzed. So if you put an UPDATE statement, delete() won't see any difference (as you said). execute() however will return a result for select statements and nil for insert/update/delete.

Collectives™ on Stack Overflow

Mysql to Rails SQL query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related