Rails association with_deleted scope doesn't work with order

Question

Here is what I got so far :

class MemberSitePosition < ActiveRecord::Base
  acts_as_paranoid
  belongs_to :member, -> { with_deleted }
  belongs_to :position
  ...
}
class Member < ActiveRecord::Base
  include ::Hierarchical
  acts_as_paranoid
  has_many   :member_site_positions
  ...
}

// Get all member associated to a site with their position
@msps = member_site_positions.includes(:position, { member { profile: :item }}).where(position_id: position_ids, member_id: member_ids)

@msps.each do |msp|
    member = msp.member
   // ... do stuff with member
end

That query work like a charm, I get all my member associated to all my site with their position.

But when I tried to order them, I got

@msps = member_site_positions.includes(:position, { member { profile: :item }}).where(position_id: position_ids, member_id: member_ids)
.order("positions.order") // adding the order

@msps.each do |msp|
    member = msp.member // Member is nil only if I pass the order (and member is deleted)
end

The member that are deleted are now nil when I try to access them.

What I discovered is that when I add the order, the generate query add

Left outer join tableX x ON x.id = main_table.x_id AND x.deleted_at IS NULL

Even with the where clause that says include x.deleted_at IS NULL OR x.deleted_at IS NOT NULL since they are rejected in the join they are not included.

I'm using rails 4.2.3 and paranoia 4.2.3 I tried to check some configuration and after a moment a found that when I used rails 4.2.1 it worked fine. I'd like to keep rails 4.2.3 if it possible.

What should I do to resolve my problem ? If anyone got an idea I'm ready to try it. Thank in advance.

---

I tried to add a joins(:position), joins(:member) and both but the result was the same

I tried to change

belongs_to :member, -> { with_deleted }

for

belongs_to :member, -> { with_deleted.unscope(where: :deleted_at) }
belongs_to :member, -> { unscope }

but it change nothing

Answer 1

In your example, you're making use of :includes which let Rails to decide if you want to use :preload, :eager_load or :joins and because you're using an attribute from the included table then it goes with the :joins. And with the above explanation, I can tell you that this is related to a current open bug in Rails https://github.com/rails/rails/issues/19226 where the "joined" table ignores your setup trying to override the default_scope. There's no workaround that I'm aware of yet