laravel/php 同じ意味のSQLで違う結果

Question

こんにちは。
売上テーブルが存在し、複数のユーザーが紐づけされています。
売上テーブルにはpublish（timestamp）があり、指定する１週間ごとの日付とユーザーで絞り込みを行いたいと考えます。

$usersには['1', '2', '3']配列。
$wvには[['2021-01-01','2021-01-02','2021-01-03'.'2021-01-04'.'2021-01-05'.'2021-01-06'.'2021-01-07'],[...............]]１週間ごとの日付。
$wv2には[['2021-01-01']['2021-01-07'],['2021-01-08']['2021-01-14']]のように週頭と週終わりを入れています。

色々な方法を試してみてはいますが、以下の２つが思い浮かびました。
①のコードは１週間分の日付をそれぞれforeachで回し、それが４週間分なので４回ループさせています。
②のコードは（①が遅かったので書きました）whereBetweenで範囲抽出を行っています。

しかしdumpすると明らかに取得できた結果が違います。。
これはどちらが正しいというか、どういう挙動でこうなっているのでしょうか？
ご存じの方いらっしゃいましたら、ご回答よろしくお願い致します。

①
            for ($i=0; $i<4; $i++){
                foreach ($wv[$i] as $key => $val) {
                    $cnt[$i] = Uriage::whereIn('id', $users)
                    ->whereDate('publish', $val)
                    ->count();
                }
            }

            dump($cnt);
            // array:4 [▼0 => 21　1 => 29　2 => 18　3 => 14]


②
            $cnt2 = array();
            foreach($wv2 as $key => $val){
                $cnt2[$key] = Uriage::whereIn('id', $users)
                ->whereBetween('publish', $val)
                ->count();
            }

            dump($cnt2);
	    // array:4 [▼0 => 171 1 => 118 2 => 160 3 => 176]

Comments

[退会済みユーザー]

publishというカラムのデータ型は何でしょうか、あとDBの処理系はmysqlでしょうか。

[tktail]

DBはposgreSQLで、publishはtimestampになります。
よろしくお願い致します。

[Orlofsky]

SQLではテーブル定義は大切です。質問にCREATE TABLEくらいは提示されては？

Answer 1

①の->whereDate('publish', $val)って動きました？ 配列で渡してOKだったっけ？と。
動いているかもう一度確認してみてください。
（可能なら、PostgreSQL全体の設定などでSQL実行ログを出力させてみてください：PostgreSQL 実行されたSQLをログに出力(システム全体の設定) - Kakiro-Web カキローウェブ）

一般に日付7つ指定するよりも、betweenで囲んだ方が速そうに思えます。

TIMESTAMP型で日付を扱う時、1秒や1秒未満のマイクロ秒とかちょっとずれてしまっても
一致しなくなってしまうと思うので、
本当に時刻成分なしに比較するなら素直にDATE型で扱った方がよろしいかと。
（比較するときにいちいちDATE型に変換するのは無駄だし。式に対するインデックスを駆使したら少しはマシかも知れませんが。）

SQLに慣れているのであれば、DB::raw(～)でSQL処理系に依存する表記で書いてしまってもいいでしょうし。
->whereRaw("publish between cast(? as timestamp) and cast(? as timestamp) + '6 days'::interval", [$val, $val])
が通じるかどうか。
（?の2箇所をそれぞれ同じ$valで置き換えさせて、クエリ上で6日分日付を足しているつもり。）

あと、余計なお世話かもしれませんが、
カラムidとpublishにはインデックスは設定済みでしょうか？
idがユーザーIDらしいので重複を許すインデックスで、
publishも同じく重複を許すインデックスでしょうね。
SQL実行ログを記録できるように設定変更できていれば、
EXPLAINの利用でクエリー実行パフォーマンスを念の為にチェックしてみると良いです。

Comments

[tktail]

ご回答ありがとうございます。
ご指摘の通り、動いていませんでした。。汗

またDB::raw(～)を実行しましたところ、betweenと同じ結果になりました。
勉強不足で理解できない部分があるので、かみ砕いて理解しようと思います。ありがとうございます。

両方の速度を算出しましたところ
whereRaw：13.865764141083
whereBetween：1.9495930671692
という結果になりました。

既存DBでインデックスは全く貼られておらず、変更も出来ないので仕方ない部分があります。。