有条件的Rails Nested加入Activerecord
技术问答
346 人阅读
|
0 人回复
|
2023-09-14
|
我正在尝试编写带有条件的嵌套联接查询。
6 I" a2 D* j; T我现在有的查询是:2 `& @0 I" _; I" o/ ?; z- |8 V. `
Event.joins(:store => :retailer).where(store: {retailer: {id: 2}})
6 e& w" ?- {1 S; e' Q输出以下SQL:# C) N7 u0 M( D7 X9 b( O0 n0 ?1 ^
SELECT "events".* FROM "events" INNER JOIN "stores" ON "stores"."id" = "events"."store_id" INNER JOIN "retailers" ON "retailers"."id" = "stores"."retailer_id" WHERE "store"."retailer_id" = '---* n1 \ h0 a) _% X; x! E5 T4 y$ j
:id: 2( `7 X& b; ^. M; H" i3 E
'
0 f) G* t0 H5 M2 X还有以下错误:
5 | K4 |8 e; W8 u& R0 U( [SQLite3::SQLException: no such column: store.retailer_id: SELECT "events".* FROM "events" INNER JOIN "stores" ON "stores"."id" = "events"."store_id" INNER JOIN "retailers" ON "retailers"."id" = "stores"."retailer_id" WHERE "store"."retailer_id" = '---8 ]8 y* _/ ~2 L. M/ S. m; J
:id: 2
1 p- } N+ @5 y" y# {'
( e) W- I+ ^& J它告诉我没有列store.retailer_id,但是,我可以运行以下查询,它将正常工作:% c8 O5 h, d4 g$ Y: ^
Event.first.store.retailer_id
: _0 P$ U+ Q* F7 u5 |' p3 Q: c/ z Event Load (0.2ms) SELECT "events".* FROM "events" ORDER BY "events"."id" ASC LIMIT 1/ j! \% s5 S* U4 |' p- E/ k1 m
Store Load (0.1ms) SELECT "stores".* FROM "stores" WHERE "stores"."id" = ? LIMIT 1 [["id", 28958]]
+ M# t r \$ R; U: X& x=> 47 M. c) T& [ ?! h
M$ d; Y- Z( m9 ^$ w解决方案:* F9 i3 D+ F: v1 l! T% B5 A+ z1 y9 R* C
/ |" z$ W, d1 h: Z! k2 u* o$ D9 d5 B* w2 T8 a& g) ?3 G( |
2 k% X9 s4 d4 r t" _9 T 看起来您不需要在这里嵌套嵌套。尝试使用类似
& } d) u7 H+ x" q5 ?- H* gEvent.joins(:store).where(stores: {retailer_id: 2})
o# x7 Y1 @: ^3 ?* L嵌套连接也可以使用 stores. c# ^& J9 r) i" E
Event.joins(:store => :retailer).where(stores: {retailer: {id: 2}}) |
|
|
|
|
|