Paranoia Gem - joins with deleted items

Question

I'm using Paranoia gem and now struggling with the problem. I need to joins has_many deleted items, but it returns not deleted only. My models:

class Mailing < ActiveRecord::Base

  acts_as_paranoid

  has_many :mailing_fields
  has_many :fields, through: :mailing_fields

end

class MailingField < ActiveRecord::

  belongs_to :mailing
  belongs_to :field

end

class Field < ActiveRecord::Base

  has_many :mailing_fields, dependent: :destroy
  has_many :mailings, through: :mailing_fields

end

Query I'm running which should return mailings with deleted items:

Field.joins(:mailings).where('mailings.id = ?', mailing_id)

score 7 · Answer 1 · answered Jan 14 '17 at 13:24

7

The only working solution I've found so far is to manually specify the JOIN:

Field.joins('INNER JOIN "mailings" ON "mailings"."id" = "fields"."mailing_id"')
     .where('mailings.id = ?', mailing_id)

answered Jan 14 '17 at 13:24

Fabian Winkler

1,401
16
24

Bruno Almeida · Answer 2 · 2022-09-15T09:21:10.583

2

unscoped also accepts a block, so you you can do the following:

Mailing.unscoped do
  Field.joins(:mailings).where('mailings.id = ?', mailing_id)
end

See: https://api.rubyonrails.org/classes/ActiveRecord/Scoping/Default/ClassMethods.html#method-i-unscoped

edited Sep 15 '22 at 09:21

answered Sep 15 '22 at 09:19

Bruno Almeida

21
2

score 1 · Accepted Answer · edited Aug 18 '20 at 05:58

1

The paranoid gem sets a default scope of only including non-deleted items in queries. The workaround would be:

Field.joins(:mailings).where('mailings.id = ? AND mailings.deleted_at IS NOT NULL', mailing_id)

edited Aug 18 '20 at 05:58

ar31an

241
5
9

answered Aug 14 '16 at 21:53

Andrew Moore

159
1
5

score 1 · Answer 4 · answered Aug 14 '16 at 22:04

You could remove the scope in your query as such:

Field.joins(:mailings).where("mailings.deleted_at != :deleted_status OR mailings.deleted_at = :deleted_status", deleted_status: nil).where(mailings: { id: mailing_id })

OR since you're trying to fetch the Field which seems to be a many to many relationship, I'd prefer to invert the query as such:

Mailing.unscoped.joins(:fields).find(mailing_id).fields

Let me know if i was able to help.

score 1 · Answer 5 · answered Aug 07 '20 at 14:15

1

I might be a bit too late to the party, but this can help if someone stumbles upon this problem.

You can create an additional association between parent and child to include the deleted records as well.

In context of the question asked,

class Field < ActiveRecord::Base
  has_many :mailing_fields, dependent: :destroy
  has_many :mailing_fields_with_deleted, -> { with_deleted }
 
  has_many :mailings, through: :mailing_fields
  has_many :mailings_with_deleted, through: :mailing_fields_with_deleted
end

and then use this relation: Field.joins(:mailings_with_deleted).where('mailings.id = ?', mailing_id)

answered Aug 07 '20 at 14:15

3minus1

149
6

I always thought this was true but I traced a bug in our code down to this scenario(mine is from a belongs_to) and found that `Field.joins(:mailings_with_deleted)` is producing sql like `SELECT * FROM fields INNER JOIN mailings.field_id ON field.id AND mailings.delete_at is NULL`. What is odd however is that if I do field.mailing_fields_with_deleted then its including the deleted items. Seems like a bug between paranoia / ActiveRecord some how to me – Calvin Oct 27 '20 at 17:33
There is a related open github issue here with further discussion https://github.com/rubysherpas/paranoia/issues/330 – Calvin Oct 27 '20 at 17:43

score -1 · Answer 6 · answered Aug 15 '16 at 01:58

-1

Paranoid gem has built-in scope to access deleted items: with_deleted.

Mailing.with_deleted.joins(:fields).where(id: mailing_id)

answered Aug 15 '16 at 01:58

Typpex

488
4
11

1

This is insufficient if the parent `MailingField` object to the `Field` object is also deleted. Even with `with_deleted`, it will still apply the `mailing_fields.deleted_at IS NULL` conditional to the join. Try it out for yourself by add `.to_sql` on the end of this. You'll see it still there. – Joshua Pinter Oct 01 '19 at 18:50

Paranoia Gem - joins with deleted items

6 Answers6