Q&A
countにorder byって何したいのか全くわかりません。
limit offsetもつける意味がないです。
前提・実現したいこと
mysqlからpostgresqlに変更した際に出力が変わってしまいました。
・予約件数の表示がshop_idと結びついていなく、reserveレコードに入っている全件をカウントしてしまう。
・予約情報が表示されない（$arr_reserveが空になっている）
reserveレコードに入っている予約ID(reserve_shop_id)とログイン時にsessionに入ったログインID（shop_id）で一致した情報を取り出して、ページング機能を使いreserveテーブルにデータが入ったのが遅い順（reserve_time）で表示させたいです。
MYSQLではSQL_CALC_FOUND_ROWSを使っていましたがPostgresqlでは使えないようなので同じように動かしたいです。
調べましたが解決策がわかりませんでした。
どう直すのか教えて頂けると助かります。
該当のソースコード
変更前のMYSQL //予約情報の取り出し SELECT SQL_CALC_FOUND_ROWS * FROM reserve WHERE reserve_shop_id=:shop_id ORDER BY reserve_time DESC limit :limit offset :offset; // 予約Total件数 SELECT FOUND_ROWS() as total;
変更後のPostgresql //予約情報の取り出し SELECT * FROM reserve WHERE reserve_shop_id=:shop_id ORDER BY reserve_time DESC limit :limit offset :offset; // 予約Total件数 SELECT COUNT(*) as total;
全体 <?php error_reporting(E_ALL); ini_set("display_errors",1); session_start(); try { //接続済み $pdo = new PDO(DSN,DB_USER,DB_PASS); $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION); // ①ユーザーIDからユーザー名を取り出す $query = "SELECT * FROM shop WHERE shop_id=:shop_id"; $shop_stmt = $pdo->prepare($query); $shop_stmt->bindValue(':shop_id', $_SESSION['user'], PDO::PARAM_INT); $shop_stmt->execute(); // ショップ情報の取り出し $row = $shop_stmt->fetch(PDO::FETCH_ASSOC); $shop_id = $row['shop_id']; $shop_name = $row['shop_name']; $yesno = $row['yesno']; $shop_comment = $row['shop_comment']; $shop_email = $row['shop_email']; // 予約情報の取り出し $sql = $pdo->prepare('select * from reserve where reserve_shop_id=:shop_id order by reserve_time desc limit :limit offset :offset'); $sql->bindValue(':shop_id', $_SESSION["user"], PDO::PARAM_INT); $sql->bindValue(":offset", $offset, PDO::PARAM_INT); $sql->bindValue(":limit", PAGE_LIMIT, PDO::PARAM_INT); $sql->execute(); $arr_reserve = []; while ($result = $sql->fetchAll(PDO::FETCH_ASSOC)){ $arr_reserve[] = [ 'reserve_shop_id' => $result['reserve_shop_id'], 'reserve_comment' => $result['reserve_comment'], 'reserve_time' => $result['reserve_time'], ]; } // 予約Total件数 $totalRow = $pdo->prepare("SELECT COUNT(*) as total from reserve"); $all = $totalRow->fetch(PDO::FETCH_ASSOC); } catch (PDOException $e) { var_dump($e); die(); } ?> <!DOCTYPE HTML> <html lang="ja"> <head> <meta charset="utf-8"> <title>トップ</title> </head> <body> <main> <p class="alert alert-success" style="text-align:center;">予約数<?=htmlspecialchars($all['total']),ENT_QUOTES,'utf-8'); ?>件</p> <table class="table table-striped"> <thead> <tr> <th>コメント</th> <th>予約時間</th> </tr> </thead> <tbody> <?php foreach($arr_reserve as $key => $value): ?> <tr> <td><?= htmlspecialchars($value['reserve_comment'], ENT_QUOTES, 'UTF-8'); ?></td> <td><?= htmlspecialchars($value['reserve_time'], ENT_QUOTES, 'UTF-8'); ?></td> <tr> <?php endforeach; ?> </tbody> </table> </main> </div> </body> </html>
試したこと
ここに問題に対して試したことを記載してください。
補足情報（FW/ツールのバージョンなど）
ここにより詳細な情報を記載してください。
そのエラーメッセージと、原因のSQL文が一致していない可能性もあるね。そのphpの中で何本SQLを実行しているのか、1本じゃないよねきっと。前後する他のSQL文も点検したほうが良いかも。確実にエラーメッセージを残すSQLなのかどうか。ってゆーか、多分そんなSQL文だったらMySQLですらエラーになっていると思うけども。
変更前のMYSQL文を追加しましたが、postgreqslで同じようにするにはどうすればいいですか？
どういう結果を出したくて組んだSQLか提示されないと憶測でしか回答できません。
また、PHPタグ付けずにプリペアドステートメントっぽいSQLを提示されても見てる人を混乱させるだけなので、きちんとPHPタグをつけてPHPコードを含めて提示するか、サンプルデータを投入したSQLにするかどちらにしてください。
質問者さんの思考の流れが全く読めない修正内容なので、丁寧に細かく一つ一つ段取りを説明していただきたい。MySQLからPostgreSQLに直すことはよくあるけど、ただそれに便乗していろんな直しを盛り込んではいないだろうかと察しているのだが。その盛り込んだ話はそもそも何をしようとしてそう直そうとしたのかも、丁寧に。
既にほぼ解決可能な回答がついているのに「まだ回答を求めています」とはどういうことでしょうか。
（2回目）
既にほぼ解決可能な回答がついているのに「まだ回答を求めています」とはどういうことでしょうか。
回答2件
0
ほぼ答えのコメントを書いたのですが、スルーされるので回答にします。
count関数は集約関数です。
データを集約した上で結果を出します。
しかも別のカラムをSELECTに使用しない限りは一件しか結果が出ません。
一件しか出ないことが決まっているのにorder byは無駄です。(しかもcountには*を指定しているので全カラム指定)
さらに。order byは並べ替えを行う機能です。並べ替えを行いたければそのカラムも何かしらの形で集約する必要があります。集約関数を使うか、group byでまとめるしかありません。
このSQLだけだされてもアプリケーションでどのように使用されるか分からないですが、「reserve_shop_idで絞りこんだ結果の全件数」を出したい、のであればorder by部分をなくして、
SELECT COUNT(*) FROM reserve WHERE reserve_shop_id=:shop_id
だけでいいです。
※どういう結果を出したくてどう調べたのかが提示されていないので出来る回答はここまでです。
追記。
DBの種類が変わるときのコツ。
以下のどれか(上から順に推奨度が低いもの)
- 方言や文法が全く変わることもあるので、今あるものをどうにかするのではなく、今あるものは一旦捨ててどう出したいデータかを考えて最初から組み直す
- 初めからそのDBにしか存在しないような機能は使わない
- フレームワークを導入する
まあ、フレームワーク導入が一番ですね。
それに今回くらいの内容だとMySQLにしかない機能を使わなくても実現可能なので(offset,limitの書き方くらい)
最低でも２番目で組んでおくべきです。
「どうしてもそのDBにしかない機能を使わなきゃいけない場面」ってそうそうなくて、汎用的に組むことは可能です。
追記されたので追記：
reserveレコードに入っている予約ID(reserve_shop_id)とログイン時にsessionに入ったログインID（shop_id）で一致した情報を取り出して、ページング機能を使いreserveテーブルにデータが入ったのが遅い順（reserve_time）で表示させたいです。
MYSQLではSQL_CALC_FOUND_ROWSを使っていましたがPostgresqlでは使えないようなので同じように動かしたいです。
count()のSQLとorder by ,offset limit を指定したSQL
2回実行すれば良いです。これならDB変わっても影響は少ないでしょう。
が、
うっすら記憶があったので探したところ、ずいぶん前のご自身の質問でOVER()とか使われてましたけど、あれは結局何だったのでしょうか。おそらくこのような記事を参考にして作ったのでしょうけど、ちゃんと組めば動きますよ（私も手元で確認しました）
SQL
1SELECT 2 * 3 ,COUNT(*) OVER() AS found_rows 4FROM 5 TEST 6WHERE 7 delete_flg = '0' 8 LIMIT 5 OFFSET 0
投稿2019/07/01 21:49
編集2019/07/02 04:36総合スコア80415
0
憶測で、
SQL
1SELECT reserve_time, COUNT(*) FROM reserve WHERE reserve_shop_id=:shop_id GROUP BY reserve_time ORDER BY reserve_time DESC;
なら指定されたreserve_shop_idのreserve_time毎の件数を表示しますが、
SQL
1SELECT COUNT(*) FROM reserve WHERE reserve_shop_id=:shop_id ORDER BY reserve_time DESC;
は指定されたreserve_shop_idの1行しか結果が出ないのでORDER BYがあっても無駄だよ、って怒られいるのでは？
同じSQLでもデータベースやそのバージョンによって方言が大きいです。SQLの観点から Oracle Database, PostgreSQL, MySQL の特徴を整理しよう！
投稿2019/07/01 20:00
総合スコア16284
