This project is archived and is in readonly mode.

#3301 ✓stale
spovich

:delete_all with named_scope fails when named_scope has :joins

Reported by spovich | September 28th, 2009 @ 07:51 PM

Trying to call #delete_all through a named_scope fails when the named_scope has conditions that join or include another model. This same method works fine for #destroy_all

For example, I use STI on User class for different types of users, and want to only #delete_all users of a specific type (in this case OemUsers).

class User < ActiveRecord::Base
 ...
 has_many :email_subscribers
 ...
end

class OemUser < User
 ...
end

class EmailSubscriber < ActiveRecord::Base
 ...
 belongs_to :user
 named_scope :oem_user, :joins => :user, :conditions => ["users.type = ?", 'OemUser']
 ...
end

Now calling delete_all will fail because the User join is NOT working for the delete clause.

EmailSubscriber.oem_user.delete_all

ActiveRecord::StatementInvalid: PGError: ERROR: missing FROM-clause entry for table "users"
LINE 1: DELETE FROM "email_subscribers" WHERE (users.type = E'Oem...

The full SQL produced is: DELETE FROM "email_subscribers" WHERE (users.type = E'OemUser')

However, destroy_all works fine:

EmailSubscriber.oem_user.destroy_all

Comments and changes to this ticket

  • spovich

    spovich September 28th, 2009 @ 08:16 PM

    Not sure if this is a limitation of ActiveRecord, but for PostgreSQL, the necessary SQL statement would need to specify the USING clause which is roughly equivalent to the FROM clause

    http://www.postgresql.org/docs/8.4/static/sql-delete.html

    So, it would need to generate the following:

    DELETE from email_subscribers USING users WHERE users.type='OemUser'

    For MySQL, it looks like you can specify a regular LEFT JOIN after the FROM clause.

  • Matt Jones

    Matt Jones September 28th, 2009 @ 10:21 PM

    The special syntax for Postgres may be part of why this isn't supported; see also #2558 for discussion on update_all, which had a similar issue.

  • spovich

    spovich September 28th, 2009 @ 11:32 PM

    Well, the general sentiment in #2558 is that you should just write the SQL which is no problem, however, named scopes are so useful and clean, that it seems a shame not to be able to use them in this way.

    Also, the documentation for #delete_all is very clear about the risks and limitations, so I don't see that as an issue.

  • Rohit Arondekar

    Rohit Arondekar October 6th, 2010 @ 06:46 AM

    • State changed from “new” to “stale”
    • Importance changed from “” to “”

    Marking ticket as stale. If this is still an issue please leave a comment with suggested changes, creating a patch with tests, rebasing an existing patch or just confirming the issue on a latest release or master/branches.

  • spovich

    spovich October 6th, 2010 @ 05:05 PM

    I no longer work at the company where I ran into this problem. Also, I'm not working on any rails 2.3.x apps (only Rails 3 now). I tested this with Rails 3 and postgresql and it works fine, so please close the issue.

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

People watching this ticket

Pages