[Rails4.2]抽出条件を絞った２つのテーブルのLeft Outer Joinの方法

Question

Rails初心者です。
RailというよりSQLの問題の気がしないでもないのですが、ある、リレーションが結ばれている２つのテーブルのLeft Outer Joinの方法について教えて下さい。
まずはシンプルにこんな感じのテーブルです。

---

<テーブル１：Item>
has_many :item_color_price

[カラム]
-id
-name

テーブル２：Item_Color_price
belongs to :item

[カラム]
-id
-color
-item_id
-price

---

で、この時に、例えば全アイテムの黄色（color)の値段が知りたい、ということで、下記の様な表を作りたいです。

item_id item_name price
1 AAA ¥200
2 BBB ¥150
3 CCC ¥ 50
4 DDD (null)
5 EEE (null)

ポイントは、Item_Color_priceテーブルのcolorで絞った条件で、ItemテーブルとLeft Joinしたいです。
（黄色を持っていないアイテムも一覧には表示したい）

@items = Item.includes(:item_color_prices).where("item_color_prices.color = Yelloow").references(:item_color_prices)

というような書き方をしても、SQLとしては、whereが最後にきてしまうため、結果としては、INNER JOINとなってしまいます。
どんなメソッド、あるいはSQLでも構わないので、書き方のアドバイスをお願いできませんでしょうか。

Answer 1

SQLで抽出するのであれば、
item_color_prices から黄色の行を取得した後で外部結合をするとよいです。
副問い合わせを利用します。

RailsのActiveRecordを用いた解決法を検討してみたのですが、
私ではよい案が見つかりませんでした、すみません。。

(Arel::Table や Arel::SelectManager など、ActiveRecord内部のクラスを利用するらしいです)

副問い合わせを用いたSQL

SELECT
  items.id AS item_id,
  items.name AS item_name,
  price
FROM
  items
LEFT JOIN (
  SELECT
    item_id,
    price
  FROM
    item_color_prices
  WHERE
    color = "yellow"
  ) yellow_prices
ON items.id = yellow_prices.item_id;

SQLite上での動作確認結果

items および item_color_prices の内容を確認したのち、
副問い合わせを用いたSQLの実行結果を確認しています。

sqlite> SELECT id, name FROM items;
1|AAA
2|BBB
3|CCC
4|DDD
5|EEE

sqlite> SELECT id, color, price FROM item_color_prices;
1|yellow|200
2|yellow|150
3|yellow|50
4|red|
5|blue|

sqlite> SELECT
   ...>   items.id AS item_id,
   ...>   items.name AS item_name,
   ...>   price
   ...> FROM
   ...>   items
   ...> LEFT JOIN (
   ...>   SELECT
   ...>     item_id,
   ...>     price
   ...>   FROM
   ...>     item_color_prices
   ...>   WHERE
   ...>     color = "yellow"
   ...>   ) yellow_prices
   ...> ON items.id = yellow_prices.item_id;
1|AAA|200
2|BBB|150
3|CCC|50
4|DDD|
5|EEE|

Comments

[MakitoKezuka]

ありがとうございます！なるほど、副問い合わせ（サブクエリ）と言うんですね。
Arel:については、調べてみましたが、どうやらleft joinは相当面倒らしいので、直接SQLを記述することとします。
ありがとうございました。