Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

This project is archived and is in readonly mode.

:delete_all with named_scope fails when named_scope has :joins

#3301

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

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

State: stale
Milestone: none
Assigned to: nobody
Importance: none

Activity

  1. spovich
    spovich

    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.

    September 28th, 2009 @ 08:16 PM

  2. Matt Jones
    Matt Jones

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

    September 28th, 2009 @ 10:21 PM

  3. spovich
    spovich

    Well, the general sentiment in #2558 Support for :joins in ActiveRecord::Base#update_all 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.

    September 28th, 2009 @ 11:32 PM

  4. Rohit Arondekar
    Rohit Arondekar
    • 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.

    October 6th, 2010 @ 06:46 AM

  5. spovich
    spovich

    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.

    October 6th, 2010 @ 05:05 PM