Simple LEFT JOIN with default_scope

[Kévin Lesénéchal]

Hey everyone,

I've got two models: Admin and CallCenter. An Admin may belong to a CallCenter (the field is nullable). To keep history, Admins and CallCenters have a deleted field with, for both of them:

default_scope { where(deleted: false) }

Now, I want to implement Admin.all_detailed which will retrieve all non-deleted admins with their call center (LEFT JOIN). Simply doing a eager_load will make a join with the default_scope condition, thus, non-deleted admins which belong to a deleted call center will be shown not having one. The unscoped_associations gem didn't solve this issue. Therefor, I did this:

def self.all_detailed
  return joins("LEFT JOIN call_centers ON call_centers.id = admins.call_center")
end

But now, when accessing the call center method of each admin, it triggers a query:

SELECT  `call_centers`.* FROM `call_centers` WHERE `call_centers`.`id` = @id LIMIT 1

I figured out the query doesn't SELECT the call center's fields:

SELECT `admins`.* FROM `admins` LEFT JOIN call_centers -- ...

So I added the SELECT fields:

def self.all_detailed
  return select("admins.*, call_centers.*")
         .joins("LEFT JOIN call_centers ON call_centers.id = admins.call_center")
end

But a query is still triggered for every access to the call_center method of Admin. It seems like Rails does not know how to link the SELECT with the Admin's association, but I don't see what else I can do.

Thanks for your help!

[Colin Law]

On 8 August 2015 at 14:45, Kévin Lesénéchal <kevin.le...@gmail.com> wrote:
> Hey everyone,
>
> I've got two models: Admin and CallCenter. An Admin may belong to a
> CallCenter (the field is nullable). To keep history, Admins and CallCenters
> have a deleted field with, for both of them:
>
> default_scope { where(deleted: false) }
>
> Now, I want to implement Admin.all_detailed which will retrieve all
> non-deleted admins with their call center (LEFT JOIN). Simply doing a
> eager_load will make a join with the default_scope condition, thus,
> non-deleted admins which belong to a deleted call center will be shown not
> having one.

I suggest that the problem may actually lie with your db design. If I
understand your question correctly you allow a non-deleted admin to
belong to a call center that that has been deleted, which seems odd.
Perhaps consider what is the status of a "deleted" call center that
still has associated admins. Is this in fact truly deleted or should
it not be considered deleted until it has no more admins?

As a side note I now avoid default_scope whenever practical, the use
of it regularly turns up problems similar to yours.

Colin