RailsのArelのTips

ActiveRecordのみでは扱いづらい複雑なSQLをRailsのArelを使って組み立てる際のTips集です。

ActiveRecordでMySQLのSQL文を発行すると
`テーブル名`.`カラム名`
というように出力されますが、わかりやすさのため曖昧にならない限りカラム名のみ記述しています。

IS NOT NULL

Hoge.where(
  Hoge.arel_table[:fuga].not_eq(nil)
)

発行されるSQL

SELECT * FROM hoges WHERE fuga IS NOT NULL;

AS

###SELECT句などで利用するAS

Hoge.select(
  Hoge.arel_table[:fuga].as('f')
)

発行されるSQL

SELECT fuga AS 'f' FROM hoges;

###JOIN句などで利用するAS

h = Hoge.arel_table.alias('h')
Fuga.joins(h).select(h[:bar])

発行されるSQL

SELECT 
  h.`bar` 
FROM 
  fugas
INNER JOIN
  hoges AS 'h' ON h.`id` = `fugas`.`hoge_id`

サブクエリ

1.FROM句へのサブクエリ

FROM句へのサブクエリでASをつける方法がわからなかった...

# 空のテーブルオブジェクトを作成する
new_table = Arel::Table.new(nil)
sql = new_table.from(
    Hoge.where(created_at: start_at...end_at).to_sql
  )
  .project(Arel.sql('*'))
  .to_sql

発行されるSQL

SELECT 
  * 
FROM 
(
  SELECT
    *
  FROM
    hoges
  WHERE
    created_at >= '2014-09-01 00:00:00' AND
    created_at < '2014-09-02 00:00:00'
)

2.JOIN句へのサブクエリ

ActiveRecordのjoinsメソッドを使える場合は文字列を直接渡すのが一番楽だと思います。
LEFT OUTER JOINなども大丈夫。
文字列を使いたくない場合などは下記の「FROM句とJOIN句の両方へのサブクエリ」と同じように記述して下さい。

sub_query = Hoge.where(created_at: start_at...end_at).to_sql
Fuga.joins("INNER JOIN (#{sub_query})")

発行されるSQL

SELECT
  *
FROM
  fugas
INNER JOIN
(
  SELECT
    *
  FROM
    hoges
  WHERE
    created_at >= '2014-09-01 00:00:00' AND
    created_at < '2014-09-02 00:00:00'
)

3.FROM句とJOIN句の両方へのサブクエリ

FROM句にサブクエリを使っていて、さらにJOIN句にもサブクエリを使いたいときは join メソッドを利用します。
このときに join メソッドには Arel::Tableオブジェクトを渡す必要があります。
具体的には下記の用に書きます。

from_table = Hoge.arel_table
  .project(
    Hoge.arel_talbe[:f_id],
    Hoge.arel_table[:name]
  )
  .as('h')

join_table = Fuga.arel_table
  .project(
    Fuga.arel_talbe[:id],
    Fuga.arel_table[:name]
  )
  .as('f')

Arel::Table.new(nil)
  .from(from_table)
  .project(
    from_table[:name],
    join_table[:name]
  )
  .join(join_table)
  .on(join_table[:id].eq(from_table[:f_id]))
  .to_sql

発行されるSQL

SELECT
  h.name,
  f.name
FROM
(
  SELECT
    `hoges`.`f_id`,
    `hoges`.`name`
  FROM
    `hoges`
) AS h
INNER JOIN 
(
  SELECT
    `fugas`.`id`,
    `fugas`.`name`
  FROM
    `fugas`
) AS f
ON
  f.`id` = h.`f_id`

##複数のSUM

Hoge.where(created_at: start_at...end_at)
  .select(Hoge.arel_table[:fuga].sum().as('f'))
  .select(Hoge.arel_table[:bar].sum().as('b'))

発行されるSQL

SELECT
  SUM(fuga) AS 'f',
  SUM(bar) AS 'b',
FROM 
  hoges
WHERE
  created_at >= '2014-09-01 00:00:00' AND
  created_at < '2014-09-02 00:00:00'

##COUNTで利用するDISTINCT

Hoge.select(Hoge.arel_table[:id].count())
  .select(Hoge.arel_table[:id].count('distinct'))

発行されるSQL

SELECT
  COUNT(id),
  COUNT(DISTINCT id)
FROM
  hoges;