Not getting any results back from a raw query

Hello, I have a raw query and when I run this query in phpMyAdmin it returns 3 results but when I try to use Laravel Query Builder I get an empty array.

My query

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
)

Result:

+----+-----------------+---------------------+
| 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 |
+----+-----------------+---------------------+

But when I try to do this query with Laravel I get back an empty array []

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);

// []

martinszeltins

6 years ago

Level 14

P.S. Here is the output of the Query Log

I can't understand why it is returning an empty array.

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
    )
)

martinszeltins

6 years ago

Best Answer

Level 14

I finally found a solution. It looks like the way I was doing it, I was passing in a single value to WHERE IN. So I remade it like so

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

Please or to participate in this conversation.