以下のような SQL があり、単体で動くことは確認済みです。
lang
1SELECT 2 account 3 , SUM(CASE WHEN color THEN face ELSE 0 END) AS color_faces 4 , SUM(CASE WHEN color THEN sheet ELSE 0 END) AS color_sheets 5 , SUM(CASE WHEN NOT color THEN face ELSE 0 END) AS mono_faces 6 , SUM(CASE WHEN NOT color THEN sheet ELSE 0 END) AS mono_sheets 7 , SUM(face) AS total_faces 8 , SUM(sheet) AS total_sheets 9FROM pr_logs 10WHERE extract(YEAR FROM print_start) = 2015 11AND extract(MONTH FROM print_start) = 5 12GROUP BY account 13ORDER BY total_faces DESC;
次はこれを CakePHP2.6.x に組み込むところで引っかかっております。
PrLog.php
lang
1 public $virtualFields = [ // find() 結果に追加したい仮想項目 2 'color_faces' => 'SUM(CASE WHEN color THEN face ELSE 0 END)' , 3 'color_sheets' => 'SUM(CASE WHEN color THEN sheet ELSE 0 END)' , 4 'mono_faces' => 'SUM(CASE WHEN NOT color THEN face ELSE 0 END)' , 5 'mono_sheets' => 'SUM(CASE WHEN NOT color THEN sheet ELSE 0 END)' , 6 'total_faces' => 'SUM(face)' , 7 'total_sheets' => 'SUM(sheet)' , 8 ]; 9
PrLogsController.php
lang
1 $this->Paginator->settings = [ 2 'fields' => [ 3 'account', 4 'color_faces' , // カラー面数合計 5 'color_sheets' , // カラー枚数合計 6 'mono_faces' , // 白黒面数合計 7 'mono_sheets' , // 白黒枚数合計 8 'total_faces' , // 面数合計 9 'total_sheets' , // 枚数合計 10 ], 11 'group' => [ 'account' ], 12 'order' => [ 'total_faces' => 'DESC' ], 13 'limit' => 15, 14 'conditions'=> [ 15 'extract(YEAR FROM print_start) = ?' => [ $year ] , 16 'extract(MONTH FROM print_start) = ?' => [ $month ] 17 ] 18 ]; 19 $results = $this->Paginator->paginate( 20 'PrLog', 21 [], 22 [ 'total_faces' ] // whitelist 23 ); 24
これを実行すると、SQL レベルでエラーになります。
lang
1Error: SQLSTATE[42703]: Undefined column: 7 ERROR: 列"CASE WHEN color THEN face ELSE 0 END"は存在しません LINE 1: ...ELECT "PrLog"."account" AS "PrLog__account", (SUM("CASE WHEN... ^ 2
構成された SQL を見ると、
SQL Query: SELECT "PrLog"."account" AS "PrLog__account", (SUM("CASE WHEN color THEN face ELSE 0 END")) AS "PrLog__color_faces", (SUM("CASE WHEN color THEN sheet ELSE 0 END")) AS "PrLog__color_sheets", (SUM("CASE WHEN NOT color THEN face ELSE 0 END")) AS "PrLog__mono_faces", (SUM("CASE WHEN NOT color THEN sheet ELSE 0 END")) AS "PrLog__mono_sheets", (SUM("face")) AS "PrLog__total_faces", (SUM("sheet")) AS "PrLog__total_sheets" FROM "public"."pr_logs" AS "PrLog" WHERE extract(YEAR FROM print_start) = 2015 AND extract(MONTH FROM print_start) = 5 GROUP BY account ORDER BY (SUM(face)) DESC LIMIT 15
集約関数の引数が無条件に二重引用符で囲まれて、リテラルとして扱われて
いるのが問題のようですが、こういった場合どう指定すればよいでしょうか?
回答1件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。