phpmyadmin Question posted in
The official documentation can be found here.

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

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:

enter image description here

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.

Tags:

2

Answers


  1. 0

    It may help to check which SQL Laravel is actually running on your database.

    The snippet bellow allows you to see just that.

    <?php

DB::listen(function ($query) {
    var_dump($query->sql);
});

Route::get('/', function () {
    DB::select('SELECT * FROM users');
});

    Also check barryvdh/laravel-debugbar extension.

    Login or Signup to reply.
  2. 0

    Try this mate, using query builder :

    DB::table('postings')
->join('medium_info', 'postings.medium_info_id', '=', 'medium_info.id')
->join('accounts', 'postings.account_id', '=', 'accounts.id')
->join('merchants', 'merchants.account_holder_id', '=', 'accounts.account_holder_id')
->join('medium_types', 'medium_types.id', '=', 'accounts.medium_type_id')
->where('merchants.account_holder_id', 230124)
->where('medium_info.id', 551678)
->where('medium_types.id', 1)
->select('medium_info.*', 'postings.posting_timestamp')
->get();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search