php - 未从 Laravel 中的原始查询返回任何结果

Question

我有一个原始查询，当我在 phpMyAdmin 中运行此查询时，它返回 3 个结果，但是当我尝试使用 Laravel 查询生成器时，我得到一个空数组。

我的查询

SELECT id, reply_text, sending_time
FROM sms_sender_inbox_replies
WHERE phone_number
IN ('+1234567819',  '+19873216154',  '+15984989898')
AND id IN (
    SELECT MAX( id ) 
    FROM sms_sender_inbox_replies
    GROUP BY phone_number
)

结果:

+----+-----------------+---------------------+
| id |   reply_text    |    sending_time     |
+----+-----------------+---------------------+
| 87 | This is a test  | 2019-07-30 08:25:26 |
| 54 | And another one | 2019-07-29 06:35:11 |
| 12 | Last test       | 2019-06-16 09:44:26 |
+----+-----------------+---------------------+

但是当我尝试使用 Laravel 执行此查询时，我得到一个空数组 []

dump($phone_numbers);

// 0 => "+1234567819"
// 1 => "+19873216154"
// 2 => "+15984989898"

$phone_numbers = implode("','", $phone_numbers);
dump($phone_numbers);


// +1234567819','+19873216154','+15984989898


dump("SELECT id, reply_text, sending_time
                  FROM sms_sender_inbox_replies
                  WHERE phone_number IN ('$phone_numbers')
                  AND id IN (
                      SELECT MAX(id)
                      FROM sms_sender_inbox_replies
                      GROUP BY phone_number
                  )");


// SELECT id, reply_text, sending_time
// FROM sms_sender_inbox_replies
// WHERE phone_number
// IN ('+1234567819',  '+19873216154',  '+15984989898')
// AND id IN (
//  SELECT MAX( id ) 
//  FROM sms_sender_inbox_replies
//  GROUP BY phone_number
// )



$replies = DB::connection('second_connection')
             ->select("
                  SELECT id, reply_text, sending_time
                  FROM sms_sender_inbox_replies
                  WHERE phone_number IN (':phone_numbers')
                  AND id IN (
                      SELECT MAX(id)
                      FROM sms_sender_inbox_replies
                      GROUP BY phone_number
                  )
             ", ['phone_numbers' => $phone_numbers]);

dump($replies);

// []

附言这是查询日志的输出

我不明白为什么它返回一个空数组。但是当我在 phpMyAdmin 中运行原始查询时，它会返回结果，所以我知道查询是正确的。

Array
(
  [0] => Array
    (
      [query] => 
          SELECT id, reply_text, sending_time
          FROM sms_sender_inbox_replies
          WHERE phone_number IN (':phone_numbers')
          AND id IN (
              SELECT MAX(id)
              FROM sms_sender_inbox_replies
              GROUP BY phone_number
          )

      [bindings] => Array
          (
              [phone_numbers] => +1234567819','+19873216154','+15984989898
          )

        [time] => 0.7
    )
)

Answer 1

当你像那样绑定(bind)它时，你会尝试将 phone_number IN 查找为单个值(而不是 3 个值)。占位符只能是单个值，不能是数组 - 但您可以使用 QueryBuilder 方法解决这个问题，这简化了绑定(bind)。

$phone_numbers = ["+1234567819", "+19873216154", "+15984989898"];

$result = DB::connection('second_connection')
            ->table('sms_sender_inbox_replies')
            ->select('id', 'reply_text', 'sending_time')
            ->whereIn('phone_number', $phone_numbers)
            ->whereIn('id', DB::table('sms_sender_inbox_replies')
                              ->selectRaw('MAX(id)')
                              ->groupBy('phone_number')
                              ->get())
            ->get();