RailsでCOALESCEを使って勤続年数を出してみた

こんにちは!kossyです!




さて、今回はRailsでCOALESCEを使って勤続年数を出してみたので、ブログに残してみたいと思います。




環境

Rails 6.0.3.4
Ruby 2.6.6
PostgreSQL 12
Docker For Mac

前提

以前書いた拙著のテーブル構成を例にとります。

kossy-web-engineer.hatenablog.com


今回はSQLのCOALESCE関数を使って勤続年数を出してみようと思います。

blog.amedama.jp

COALESCE関数は、「いくつかの値の中で最初にNULLでない値」を返すSQL関数です。
また、「NULLの値を任意の値に置き換える」ということもできます。

勤続年数の場合、導出データのため、そのままカラムを用意して保存するというケースはないと思います。
もしカラムを用意して保存するとなると、そのうち更新しなくてはいけなくなるからです。
同じ理由で「年齢」もカラムを用意して保存することはないですね。

導出データについては以下が詳しかったです。
blog.codecamp.jp

勤続年数を算出するには、「入社日」が必要ですが、今回の例ではhired_atがそれに当たります。
社員を管理するようなアプリの場合、退職している方もいらっしゃると思いますから、retired_atというカラムを用意しています。

この時、退職していない社員の方は、retired_atの値がnullのため、特に値を加工しないで勤続年数を求めようとする場合、

null - hired_at

のような計算になってしまい、エラーになってしまいます。

そんな時、COALESCE関数が大活躍します。

  scope :length_of_service, -> () {
    query = <<-SQL
      SELECT id, last_name, first_name,
      (COALESCE(retired_at, current_date) - hired_at) / 365 as length_of_service
      FROM employees
      ORDER BY id ASC
    SQL

    find_by_sql(query)
  }

上記のクエリは、COALESCE関数を使ってretired_atの値をそのまま使うか、nullであればPostgreSQLのcurrent_date関数で、当日の日付を使っています。
なので、retired_atかcurrent_date - hired_atという計算がどの行のレコードでも行うことができます。

COALESCE関数、シンプルですがかなり強力な関数ですね。




勉強になりました。