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.
Monday, November 25, 2013
Subscribe to:
Posts (Atom)