Using query builder or eloquent where adding statements with conditions

[tombombadilll]

• Laravel Version: 5.4.15
• PHP Version: 7.0.15
• Database Driver & Version: Ver 14.14 Distrib 5.5.44, for Win32 (x86)
• Running Zend Server 9.0.2 (Apache, Mysql)

Description:

I have a very weird issue when adding where conditions to a query based on a switch. Apartment is just a Eloquent class. Have also tried this with DB::table(...) too. Same issue.

$query = Apartment::query()->select('item_id');
        foreach($searchParams as $key => $value) {
            switch($key) {
                case 'searchable':
                    $query->where($key, $value);
                    break;
                case 'type':
                    $query->where($key, $value);
                    break;
                case 'max_rent':
                    $query->where('rent', '<=', $value);
                    break;
                case 'min_area':
                    $query->where('area', '>=', $value);
                    break;
                case 'min_room':
                    $query->where('room', '>=', $value);
                    break;
                default:
                    throw new Exception('Illegal option:'.$key);
            }
        }
                print_r($searchParams);
        print_r($query->getBindings());
        echo $query->toSql();
        print_r($query->get()->toArray()); die;

Steps To Reproduce:

This code prints out:

# SearchParams variable
Array
(
    [searchable] => 1
    [type] => 2
    [max_rent] => 11000
    [min_room] => 1
    [min_area] => 20
    [item_type] => Array
        (
            [0] => 1-1
            [1] => 1-2
        )

    [item_id__not] => 60
    [user_id__not] => 44
    [region] => Array
        (
            [0] => 124
            [1] => 128
            [2] => 760
        )

)
# Bindings
Array
(
    [0] => 1
    [1] => 2
    [2] => 11000
    [3] => 1
    [4] => 20
)

toSql query

`select `item_id` from `apartment` where `searchable` = ? and `type` = ? and `rent` <= ? and `room` >= ? and `area` >= ?`

This produces the query below: (Checked both with DB::getQueryLog() and zray in Zend Server (which list all queries for a request)

`select `item_id` from `apartment` where `searchable` = 1 and `type` = 20 and `rent` <= 11000 and `room` >= 1 and `area` >= 2`

Check the order of the binding array values (1,2,11000,1,20) When they are added to the query they come in the order (1,20,11000,1,2) ???!!!

It is very strange since the getBindings are in the correct order. Then when it produces the query they come in the wrong order. If I comment out one more condition in the switch it works. As soon as they are more than four it seems to get corrupt.

[edenreich]

I want to reproduce this issue can you tell me how your apartment table looks like? a copy of your migration file will be good.

[edenreich]

remove the exception and see if it binds correctly.

I've tested it and I got the following query: select item_id from apartments where searchable = 1 and type = 2 and rent <= 11000 and room >= 1 and area >= 20

[tombombadilll]

CREATE TABLE IF NOT EXISTSapartment( idint(10) unsigned NOT NULL AUTO_INCREMENT, searchabletinyint(1) NOT NULL DEFAULT '1', typetinyint(2) unsigned NOT NULL, created_atdatetime NOT NULL, updated_atdatetime NOT NULL, roomfloat NOT NULL, rentfloat NOT NULL, areafloat NOT NULL, floor_levelfloat DEFAULT NULL, descriptiontext, streetvarchar(100) DEFAULT NULL, postal_codevarchar(6) DEFAULT NULL, postal_placevarchar(100) DEFAULT NULL, landlordvarchar(100) DEFAULT NULL, item_idint(10) unsigned DEFAULT NULL, PRIMARY KEY (id), KEYite_id(item_id), CONSTRAINTapartment_item_idFOREIGN KEY (item_id) REFERENCESitem(id) ) ENGINE=InnoDB AUTO_INCREMENT=259 DEFAULT CHARSET=utf8;

I also tried to remove the Exception but since it doesn't end up there it didn't make a difference.

[tombombadilll]

I have 15+ years working with php so I don't understand why it is behaving like this. Really strange that the params get randomly sorted. I can't see any errors in the code either. Was looking for a missing break or something but don't find anything that explains the behavior. I will do some more testing now to see if I can find any solution.

[tombombadilll]

Did some more testing without the switch. This code works correctly

$query = DB::table('apartment')->select('item_id')
            ->where('searchable', 1)
            ->where('type', 2)
            //->where('rent', '<=', 11000)
            ->where('room', '>=', 1)
            //->where('area', '>=', 20)
            ->get();

But when I un-comment any of the two lines that are a comment the order becomes corrupt.

[tombombadilll]

Any updates on this or suggestions on how I find the error? I had to switch to using DB::select instead to make this work.

[themsaid]

I can't reproduce your issue:

DB::enableQueryLog();

DB::table('apartment')->select('item_id')
    ->where('searchable', 1)
    ->where('type', 2)
    ->where('rent', '<=', 11000)
    ->where('room', '>=', 1)
    ->where('area', '>=', 20)
    ->get();

dd(DB::getQueryLog());

The log has:

array:1 [▼
  0 => array:3 [▼
    "query" => "select `item_id` from `apartment` where `searchable` = ? and `type` = ? and `rent` <= ? and `room` >= ? and `area` >= ?"
    "bindings" => array:5 [▼
      0 => 1
      1 => 2
      2 => 11000
      3 => 1
      4 => 20
    ]
    "time" => 2.8
  ]
]

Which is the correct order of bindings and parameters.

[tombombadilll]

Thanks for the update. I also get the correct order when calling getBindings() so maybe this is outside of Laravel or what do you think? When the actual query occurs I get the wrong order in both Zend Server z-ray and in the dd(DB::getQueryLog()); command.

I run several Laravel projects on my computer and haven't had any issues like this before.

Any ideas how to debug this? I mean if getBindings() is correct, what's next to check?

[themsaid]

What i'm sharing is actually the result of dd(DB::getQueryLog());, not what might mess with the bindings order like that, can't see anything in the code that might re-order.

Can you try to run the same query on a fresh laravel installation? If everything went well then maybe something in your code caused this behaviour.

[themsaid]

Closing for lack of activity and also for being unable to replicate.

[tombombadilll]

I seem to have missed your last update on this but will try this query on a fresh install just to debug and see if the same thing happens.