Laravel DB::select() does not return "correct" data

I am using Laravel 8.12

I am doing a DB::select() call with DB::raw() method filtering. But for convenience I will post full statement with values as well.

Here is the PHP code for query

$sql = "SELECT `medium_info`.* , `postings`.`posting_timestamp` FROM `postings` INNER JOIN `medium_info` ON `postings`.`medium_info_id` = `medium_info`.`id` INNER JOIN `accounts` ON `accounts`.`id` = `postings`.`account_id` INNER JOIN `merchants` ON `merchants`.`account_holder_id` = `accounts`.`account_holder_id` INNER JOIN `medium_types` ON `medium_types`.`id` = `accounts`.`medium_type_id` WHERE `merchants`.`account_holder_id` = :merchant_account_holder_id AND `medium_info`.`id` = :medium_info_id AND `medium_types`.`id` = :medium_types_id";    $result = DB::select ( DB::raw($sql), ['merchant_account_holder_id'=>230124, 'medium_info_id'=>551678, 'medium_types_id'=>1] );  

When I print $result it gives me data like this:

[0] => stdClass Object                  (                      [id] => 230124                      [purchase_date] => 2020-11-22                      [redemption_date] =>                       [expiration_date] =>                       [hold_until] => 2021-05-07 02:30:08                        ...more medium_info data here                        [posting_timestamp] => 2020-11-25 23:27:13                        ...merchants table data which I did not request                        [account_holder_id] => 230124                      [name] => Best Buy                      [logo] => /cdn/merchants/230124/logo.png  

If I do the following, results are still same:

$sql = "SELECT `medium_info`.* , `postings`.`posting_timestamp` FROM `postings` INNER JOIN `medium_info` ON `postings`.`medium_info_id` = `medium_info`.`id` INNER JOIN `accounts` ON `accounts`.`id` = `postings`.`account_id` INNER JOIN `merchants` ON `merchants`.`account_holder_id` = `accounts`.`account_holder_id` INNER JOIN `medium_types` ON `medium_types`.`id` = `accounts`.`medium_type_id` WHERE `merchants`.`account_holder_id` = 230124 AND `medium_info`.`id` = 551678 AND `medium_types`.`id` = 1";  $result = DB::select ( $sql));  

However when I run this query in phpMyAdmin it gives me 'correct' results with "id" from medium_info table. Here is a screenshot:

I want to add here that results received via DB::select() query has "merchants" row attached which I did not request in my query. Even if I just do SELECT `postings`.`posting_timestamp` FROM... request it would give me this result:

Array  (       [0] => stdClass Object       (            [posting_timestamp] => 2020-11-25 23:27:13            [account_holder_id] => 230124            [id] => 230124            [name] => Best Buy            [logo] => /cdn/merchants/230124/logo.png            [description] => <p>When technology meets life, they come together at Best Buy&reg;. Best Buy has the technology that&rsquo;s fun and functional, from tablets and videogames, to appliances and big screen TVs. Use your gift card at BestBuy.com&reg; or at any US Best Buy store.</p>            [website] => http://www.bestbuy.com            [merchant_code] => BES            [is_premium] => 1            [large_icon] => /cdn/merchants/230124/large_icon.png            [status] => 1            [get_gift_codes_from_root] => 0            [website_is_redemption_url] => 0            [cost_to_program] => 0            [toa_name] =>        )  )  

So obviously it is attaching "merchants" row no matter what I "SELECT". Also, notice that there is [id] => 230124 which is coming from "nowhere", there is no field id in merchants table. There is an id field in medium_info table but it should have returned 551678 not 230124 which is merchant id with field name account_holder_id in merchants table.

Edit: Just want to add that it does not attach merchants data when I run it in phpMyAdmin.

I am still trying to figure this out. If you need more info I am ready to provide. This must be something to do with Laravel DB::select conventions which I am not understanding, since it works in phpMyAdmin? Any help is appreciated.

https://stackoverflow.com/questions/67428671/laravel-dbselect-does-not-return-correct-data May 07, 2021 at 11:32AM