QA@IT

GROUP BYを使ったクエリをArel (ActiveRecord) で書くには?

5690 PV

Railsで習作アプリを作っています。PostgreSQLを使っていて、以下のようなスキーマがあります。日付と距離(水泳の記録です)、及びコメントを記録するだけのものです。

mswim-development-# \d swims
                                     Table "public.swims"
   Column   |            Type             |                     Modifiers                      
------------+-----------------------------+----------------------------------------------------
 id         | integer                     | not null default nextval('swims_id_seq'::regclass)
 length     | integer                     | 
 swam_at    | date                        | 
 note       | text                        | 
 created_at | timestamp without time zone | not null
 updated_at | timestamp without time zone | not null
Indexes:
    "swims_pkey" PRIMARY KEY, btree (id)

月ごとの合計距離を出したいのですが、以下のようなSQLをRailsから発行したいのですが、やり方が分かりません。

mswim-development-# SELECT to_char(swam_at, 'yyyy-mm') AS month, sum(length) FROM "swims" GROUP BY month ORDER BY month;
  month  |  sum
---------+-------
 2012-01 |  3400
 2012-02 |  2800
 2012-03 |  3800
 2012-04 |  6150
 2012-05 | 12800
 2012-06 | 15500
 2012-07 | 12200
(7 rows)

こんな感じでHashを返してくれるかなと書いてみたのは、

@months = Swim.group("to_char(swam_at, 'yyyymm')").sum(:length)

ですが、

> Swim.group("to_char(swam_at, 'yyyymm')").sum(:length)
(0.9ms)  SELECT SUM("swims"."length") AS sum_length, to_char(swam_at, 'yyyymm') AS to_char_swam_at_yyyymm FROM "swims" GROUP BY to_char(swam_at, 'yyyymm') ORDER BY swam_at DESC

PG::Error: ERROR:  column "swims.swam_at" must appear in the GROUP BY clause or be used in an aggregate function

とエラーで怒られます。何を間違えているのかお分かりの方がいれば、教えていただけませんでしょうか。

  • ご存知かもしれませんが、sqliteであれば下記でいけますね。
    Swim.group("STRFTIME('%Y%m', swam_at)").sum(:length)
    PostgreSQLに限った話なんでしょうか。
    -
  • ありがとうございます。実は最初、手元のsqliteで、まさにそう書きました。Heroku(postgresです)にpushしてアプリがコケてから、developmentとproductionの環境を一致させることの意味と、ORMがDBの違いを吸収するということが幻想だということの意味を理解しました。 -

回答

よくある、「GROUP BYに書けるのは集約関数と(以下略」ではないのですね。

試してみましたが、こちらではとくに問題なく実行できてしまいました。(再現に使ったアプリを作成するためのテンプレート)

$ rails new mswim -d postgresql --skip-bundle -m https://raw.github.com/gist/3156194/974a0bf55ae74110da25c8d7ac9cac39aee95f64/gistfile1.rb

$ cd mswim
$ bundle exec rake about
About your application's environment
Ruby version              1.9.3 (x86_64-darwin11.4.2)
:
Rails version             3.2.6
:
Environment               development
Database adapter          postgresql
:

$ bundle show
:
  * pg (0.14.0)

$ port installed postgresql91\*     
The following ports are currently installed:
  postgresql91 @9.1.4_0 (active)
  postgresql91-server @9.1.4_0 (active)

$ script/rails dbconsole
mswim_development=> \d swims
                                     Table "public.swims"
   Column   |            Type             |                     Modifiers                      
------------+-----------------------------+----------------------------------------------------
 id         | integer                     | not null default nextval('swims_id_seq'::regclass)
 length     | integer                     | 
 swam_at    | date                        | 
 note       | text                        | 
 created_at | timestamp without time zone | not null
 updated_at | timestamp without time zone | not null
     Indexes:
        "swims_pkey" PRIMARY KEY, btree (id)

$ script/rails console
Loading development environment (Rails 3.2.6)
>> Swim.group("to_char(swam_at, 'yyyymm')").sum(:length)
   (0.1ms)  SHOW max_identifier_length
   (3.0ms)  SELECT SUM("swims"."length") AS sum_length,
        to_char(swam_at, 'yyyymm') AS to_char_swam_at_yyyymm
        FROM "swims" GROUP BY to_char(swam_at, 'yyyymm')
=> {"201201"=>29000, "201207"=>16000, "201206"=>22100, "201204"=>22200, "201203"=>21500, "201205"=>22500, "201202"=>18900}
編集 履歴 (0)
  • うわ、再現、ありがとうございます。というか再現しなかったのですね。間違い探しのようですが、ちょっと調べてみます。pgやpostgresのバージョンが違うような。 -
  • Swimモデルに書いた「default_scope order('swam_at DESC')」をコメントアウトすれば上手くいきました! クエリAPIの使い方を間違っているとばかり思っていましたが、再現していただいことで気付きました。 -
ウォッチ

この質問への回答やコメントをメールでお知らせします。