mysql - has_many through doesn't work when using same table

Question

I have a model FamilyService. In the model I have these associations:

belongs_to :family
has_many :balance_histories, :through => :family
has_many :balances, :through => :family

Executing this command: FamilyService.where("families.id > 90000").joins(:balances) works fine. I get:

SELECT families_services.*
FROM families_services
INNER JOIN families ON families.id = families_services.family_id
INNER JOIN balances ON balances.family_id = families.id
WHERE (families.id > 90000)

Also, executing this query: FamilyService.where("families.id > 90000").joins(:balance_histories) gives me this:

SELECT families_services.*
FROM families_services
INNER JOIN families ON families.id = families_services.family_id
INNER JOIN balance_histories ON balance_histories.family_id = families.id
WHERE (families.id > 90000)

Great!

But when trying to do: FamilyService.where("families.id > 90000").joins(:balances, :balance_histories). I get:

SELECT families_services.*
FROM families_services
INNER JOIN families ON families.id = families_services.family_id
INNER JOIN balances ON balances.family_id = families.id
INNER JOIN families families_families_services_join ON families_families_services_join.id = families_services.family_id
INNER JOIN balance_histories ON balance_histories.family_id = families_families_services_join.id
WHERE (families.id > 90000)

Which is wrong (families families_families_services_join ON families_families_services_join.id). How to fix?

Answer 1

The generated query is actually correct. The line

INNER JOIN families families_families_services_join ON ...

is just temporarily assigning the name families_families_services_join to the table families so the query is clearer.

You can, however, simplify the query by specifying your joins query as,

FamilyService.where("families.id > 90000").joins(family: [:balances, :balance_histories])

This will generate something like

SELECT families_services.*
FROM families_services
INNER JOIN families ON families.id = families_services.family_id
INNER JOIN balances ON balances.family_id = families.id
INNER JOIN balance_histories ON balance_histories.family_id = families.id
WHERE (families.id > 90000)