Filter parents by child attribute, but eager

That title is a bit obtuse, so here's an example. Suppose we have a Rails 3 app with models Ship , Pirate , and Parrot . A ship has_many pirates, and a pirate has_many parrots.

Ship.includes(pirates: :parrots).where('parrots.name LIKE ?', '%polly%')

This returns ships having at least one pirate with at least one parrot whose name is like "polly". I would also like it to eager-load all of the pirates and parrots for those ships... but in reality only the pirates with matching parrots are eager-loaded, and among those, only the matching parrots are eager-loaded. The generated SQL is something like this:

SELECT ships.id AS t0_r0, ships.name AS t0_r1, pirates.id AS t1_r0, pirates.name AS t1_r1, parrots.id AS t2_r0, parrots.name AS t2_r1 FROM ships LEFT OUTER JOIN pirates ON pirates.ship_id = ships.id LEFT OUTER JOIN parrots ON parrots.pirate_id = pirates.id WHERE (parrots.name LIKE '%polly%')

When doing Ship.includes(pirates: :parrots) without the condition, ActiveRecord generates a bundle of queries that is somewhat closer to what I want:

SELECT ships.* FROM ships
SELECT pirates.* FROM pirates WHERE pirates.ship_id IN (ship IDs from previous query)
SELECT parrots.* FROM parrots WHERE parrots.pirate_id IN (pirate IDs from previous query)

If I could somehow change that first query to use the SQL from the first example, it would do exactly what I want:

SELECT ships.* FROM ships LEFT OUTER JOIN pirates ON pirates.ship_id = ships.id LEFT OUTER JOIN parrots ON parrots.pirate_id = pirates.id WHERE (parrots.name LIKE '%polly%')
SELECT pirates.* FROM pirates WHERE pirates.ship_id IN (ship IDs from previous query)
SELECT parrots.* FROM parrots WHERE parrots.pirate_id IN (pirate IDs from previous query)

But I'm not aware of any way to get ActiveRecord to do this, or any way to do it myself and "manually" wire up the eager-loading (which is necessary in my situation to avoid an N+1 query explosion). Any ideas or advice would be appreciated.

---

Ship.joins(pirates: :parrots).where('parrots.name LIKE ?', '%polly%').preload(pirates: :parrots)

需要导轨3+

---

If INNER JOIN is what you're looking for, I think

Ship.includes(pirates: :parrots).where('parrots.name LIKE ?', '%polly%').joins(pirates: :parrots)

gets it done.

链接地址: http://www.djcxy.com/p/39534.html

上一篇: 无法嵌套JSON Rails

下一篇: 过滤父母的孩子属性，但渴望