

# 概要

Railsでviewに渡すためのActiveRecord::Relationオブジェクトを準備したいのですが、意図通りに集計できません。`user_id`
毎の最新レコードを集計するというものです。

# 詳細

Statusという入退室モデルがあり、次のような構造をしています。

  * Status 
    * id
    * user_id
    * logged_in
    * logged_out
    * created_at
    * updated_at

各ユーザ (`user_id`) の最新レコード（最後に入室もしくは退室した時間）をviewでユーザ毎に表示させたいのですが、集計の方法がわかりません。

ご教示のほどよろしくお願いします。

# 動作環境

  * Rails 4.2.5
  * ruby 2.2.3


CC BY-SA 3.0
2015-12-07T15:58:35.637
19728
2016-01-26T21:32:19.420
2015-12-07T17:43:38.290
76
12459
question
ruby-on-rails ruby rails-activerecord
user_id 別の最新レコードを集計する方法


サブクエリを作ることで user_id ごとに集計して、その中でlogged_in が最大の行を選択することができます。

[code]

    Status
      .where(%{
        NOT EXISTS(
          SELECT 1
          FROM statuses s1
          WHERE statuses.user_id = s1.user_id AND
                statuses.logged_in < s1.logged_in
        )
      }).order(:user_id).pluck(:user_id, :logged_in)
    
[/code]

これを実行すると、次のようなSQLクエリが実行されます。

[code]

    SELECT *
    FROM statuses
    WHERE (
      NOT EXISTS(
        SELECT 1
        FROM statuses s1
        WHERE statuses.user_id = s1.user_id AND
              statuses.logged_in < s1.logged_in
      )
    )
    
[/code]

※ARELを使って抽象化した方が良いのですが、テーブルを別名でサブクエリに入れる方法が分からない… (多分できない)

なお、`user_id` と `logged_in` の最大値だけを求めれば良いのであれば、イカのようなコードで取得は出来ます。この場合、`id` などが
`nil` になりますので、気をつけてください。

[code]

    Status
      .group(:user_id)
      .select("user_id, MAX(logged_in) AS logged_in")
    
[/code]

[code]

    SELECT user_id, MAX(logged_in) AS logged_in
    FROM statuses
    GROUP BY user_id
    
[/code]

`user_id` と `logged_in`
以外のフィールドも欲しいと思って、イカのように書いてしまうとSQLの文法エラーになります。これは、`SELECT` 句には、`GROUP BY`
に指定したフィールドと、集約関数以外は指定できないためです。

利用するRDBMSによってはエラーを返しませんが、 **意図通りに動くかはマニュアルを参照してください** 。

[code]

    Status
      .group(:user_id)
      .select("*, MAX(logged_in) AS max_logged_in")
    
[/code]

[code]

    SELECT *, MAX(logged_in) AS max_logged_in
    FROM statuses
    GROUP BY user_id
    
[/code]


CC BY-SA 3.0
2015-12-07T17:41:15.077
19732
2016-01-26T21:32:19.420
2016-01-26T21:32:19.420
85
76
19728
answer


できれば使用しているRDBMSとサンプルデータ、それと期待する結果も載せてほしかったです。  
とりあえず、「たぶんこういうことかな？」と想像しながら回答します。

まずこんなテストコードを書きました。

[code]

    require 'test_helper'
    
    class StatusTest < ActiveSupport::TestCase
      test "ユーザー別に最新のレコードを集計する" do
        alice = User.create!(name: 'Alice')
        bob = User.create!(name: 'Bob')
    
        alice_status_1 = alice.statuses.create!(
          logged_in: '2015-01-01 00:00:00', logged_out: '2015-01-01 01:00:00')
        alice_status_2 = alice.statuses.create!(
          logged_in: '2015-01-02 00:00:00', logged_out: nil) # <= 最新
    
        bob_status_1 = bob.statuses.create!(
          logged_in: '2015-01-01 00:00:00', logged_out: '2015-01-31 01:00:00') # <= 最新
        bob_status_2 = bob.statuses.create!(
          logged_in: '2015-01-02 00:00:00', logged_out: '2015-01-02 01:00:00')
    
        statuses = Status.latest_by_user.order(:user_id)
        assert_equal [alice_status_2, bob_status_1], statuses
      end
    end
    
[/code]

このテストコードが期待する結果と合っていれば、以下のようなコードで実装できます。

[code]

    class Status < ActiveRecord::Base
      belongs_to :user
    
      scope :latest_by_user, -> {
        sub_query = <<-SQL
    SELECT user_id,
           MAX(CASE WHEN logged_out IS NULL THEN logged_in ELSE logged_out END) AS max_datetime
    FROM statuses
    GROUP BY user_id
        SQL
    
        join_sql = <<-SQL
    INNER JOIN (#{sub_query}) AS sub
    ON sub.user_id = statuses.user_id
    AND sub.max_datetime = CASE WHEN statuses.logged_out IS NULL THEN statuses.logged_in ELSE statuses.logged_out END
        SQL
    
        joins(join_sql)
      }
    end
    
[/code]

userd_idごとにlogged_inまたはlogged_outの最大時間を取得し（`sub_query`）、それをStatusのjoinの中で絞り込み条件にしています。（`join_sql`）

ときどき「生SQLは書きたくない！」という人を見かけますが、集計関数（SUM,
MAX等）が絡んでくると急激にActiveRecordが書きにくくなるので、無理せずにSQLを書けばいいじゃないか、というのが僕の実装方針です。

動作確認に使用したのはSQLiteです。  
標準SQLの範囲内だと思うのでPostgreSQLやMySQLでも動作すると思いますが、確認はしていません。

GitHubにコードもアップしたので、必要であれば手元で動かしてみてください。

<https://github.com/JunichiIto/show-latest-sandbox>


CC BY-SA 3.0
2015-12-07T20:22:36.770
19736
2015-12-07T20:22:36.770
85
19728
answer


集計というのが何を指しているのか分かりませんが、idがautoincrementなカラムでユーザの最新のデータを取得するだけであれば

[code]

    Status.where(user_id: user_id).order(:id).reverse_order.limit(1)
    
[/code]

で良いのでは無いかと思いますがいかがでしょうか？


CC BY-SA 3.0
2015-12-27T10:13:15.353
20412
2015-12-27T10:13:15.353
7616
19728
answer