php - 这些 Laravel 查询绑定(bind)不起作用

Question

为什么我的 Laravel 查询绑定(bind)不起作用？

此查询在 MySqlWorkbench 中有效:

SELECT 
    COUNT(id) AS AC_Leads,
    SUM(IF(webinarMinsWatched > 0, 1, 0)) AS startedWebinar,
    SUM(IF(webinarMinsWatched >= 47.23, 1, 0)) AS reachedCta
FROM
    contacts
WHERE
    DATE_FORMAT(created_at, '%Y-%m-%d') = '2017-05-06'
        AND adId = '6000689619221'
GROUP BY adId

这是我的 Laravel 代码。不幸的是，它返回一个空数组(与上面的原始 MySql 不同，它返回正确的结果):

$webinarCta = '47.23';
$adId = "6000689619221";
$dateStr = "2017-05-06";
DB::enableQueryLog();
$statsQuery = DB::table('contacts')
        ->selectRaw('COUNT(id) as AC_Leads, SUM(IF(webinarMinsWatched > 0, 1, 0)) as startedWebinar, SUM(IF(webinarMinsWatched >= ?, 1, 0)) as reachedCta', [(float) $webinarCta])
        ->whereRaw("DATE_FORMAT(created_at, '%Y-%m-%d') = '?'", [$dateStr])
        ->whereRaw("adId = '?'", [(int) $adId])
        ->groupBy('adId');
print_r($statsQuery->toSql());
echo '<hr>';
$stats = $statsQuery->get();
print_r($stats);
echo '<hr>';
Log::debug(DB::getQueryLog());
dd(DB::getQueryLog());

我还尝试使用“命名绑定(bind)”，如图所示 in the docs .

有趣的是，当我注释掉 2 whereRaw 行时，我确实得到了一个结果数组(尽管它是不正确的)。

根据DB::getQueryLog()，绑定(bind)看起来不错；这是日志输出:

array:1 [▼
  0 => array:3 [▼
    "query" => "select COUNT(id) as AC_Leads, SUM(IF(webinarMinsWatched > 0, 1, 0)) as startedWebinar, SUM(IF(webinarMinsWatched >= ?, 1, 0)) as reachedCta from `contacts` where DATE_FORMAT(created_at, '%Y-%m-%d') = '?' and adId = '?' group by `adId` ◀"
    "bindings" => array:3 [▼
      0 => 47.23
      1 => "2017-05-06"
      2 => 6000689619221
    ]
    "time" => 0.38
  ]
]

Answer 1

这是最终对我有用的查询。 ? 周围没有引号。

    $statsQuery = DB::table('contacts')
            ->selectRaw('adId, COUNT(id) as AC_Leads, SUM(IF(webinarMinsWatched > 0, 1, 0)) as startedWebinar, SUM(IF(webinarMinsWatched >= ?, 1, 0)) as reachedCta', [(float) self::WEBINAR_CTA]) 
            ->groupBy('adId');
    if ($startDate) {
        $statsQuery->whereRaw("CONVERT_TZ(created_at, 'UTC', ?) >= STR_TO_DATE(?, '%Y-%m-%d')", [$reportingClientTimeZone, $startDate]); //https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz
    }
    if ($endDate) {
        $statsQuery->whereRaw("CONVERT_TZ(created_at, 'UTC', ?) < DATE_ADD(STR_TO_DATE(?, '%Y-%m-%d'), INTERVAL 1 day)", [$reportingClientTimeZone, $endDate]); //Given a certain endDate (which begins at midnight), only include results LESS THAN 1 day after that midnight
    }
    $result = $statsQuery->get();

谢谢，@Razor。