Active Record - joins + include Methods Causing an Unintended Join

I recently fixed a problem in my Rails 3.2 app where I was using both the joins and include methods in an Active Record query, and it was triggering a join that I didn't want.  WTF?  Why are you using include and joins, and you don't want a join?

I needed to run a query on table A and I needed to apply criteria against another table B.  Thus, I needed to (inner) join those two with the joins method.    For the rows of A that met the search criteria, I wanted to eagerly the corresponding rows from tables X, Y, and Z.  Of course, I wanted to avoid a 3N+1 query situation.  So, I also used the includes method.

Typically, the includes method generates a query by IDs for the related objects.  In my case, I was getting four INNER JOINs - one each for B, X, Y, and Z.  Under "normal" circumstance, maybe this would have been OK, but my problem is table Y is in a separate database, and you can't join across databases.  (You can't really do transactions across databases, either.)

My original code used an array of named associations in the joins method - joins(:bs).  On a lark, I decided to recode it to use a string - joins('INNER JOIN bs ON bs.a_id = as.id'), and it worked:  I got the inner join for B and three individual queries for X, Y, and Z.  Because Y is queried as a simple query with an array of IDs, the fact that Y is in another database isn't a problem - it just works.

Anyway, if you've stumbled across this post while trying to solve the same problem, I hope this helps.

Charles.