-
Notifications
You must be signed in to change notification settings - Fork 1.5k
OrWhere is return wrong result #3337
-
I have a query
self::select($selected_field) ->where(['status'=>true,'reporting_method'=> 3]) ->orWhere(['status'=>true,'reporting_method'=> '3']) ->get();
it should return results where reporting_method is either 3 or '3' with status: true
But it returns all status: true records
Here is the dump of QueryLog
array:1 [ 0 => array:3 [ "query" => "{ "find" :"reporting_method", "filter" : { "$or" : [ { "$and" : [ { "status" : true }, { "reporting_method" : { "$numberInt" : "3" } } ] }, { "$or" : [ { "status" : true }, { "reporting_method" : "3" } ] } ] }, "projection" : { "_id" : true, "name" : true, "reporting_method" : true } }" "bindings" => [] "time" => 234 ] ]
It was working well and good in Lumen 5 but in lumen 10 it is not working well.
Please check
Beta Was this translation helpful? Give feedback.
All reactions
By using an array of criteria, Laravel splits them into a list of "where" clauses.
Without the MongoDB override, the SQL generated by your query is the following:
select * from "users" where ("status" = ? and "reporting_method" = ?) or ("status" = ? or "reporting_method" = ?)
The query generated by the MongoDB Query Builder is consistent with the Laravel behavior:
{
"$or": [
{
"$and": [
{
"status": true
},
{
"reporting_method": 3
}
]
},
{
"$or": [
{
"status": true
},
...Replies: 2 comments 1 reply
-
By using an array of criteria, Laravel splits them into a list of "where" clauses.
Without the MongoDB override, the SQL generated by your query is the following:
select * from "users" where ("status" = ? and "reporting_method" = ?) or ("status" = ? or "reporting_method" = ?)
The query generated by the MongoDB Query Builder is consistent with the Laravel behavior:
{
"$or": [
{
"$and": [
{
"status": true
},
{
"reporting_method": 3
}
]
},
{
"$or": [
{
"status": true
},
{
"reporting_method": "3"
}
]
}
]
}In order to get a $and in the second criteria, you have to create a nested subquery:
self::select($selected_field) ->where(['status' => true, 'reporting_method' => 3]) ->orWhere(fn (Builder $builder) => $builder->where(['status' => true, 'reporting_method' => '3'])) ->get();
You can also write all the query in the same where:
self::select($selected_field) ->where(['$or' => [ ['status' => true, 'reporting_method' => 3]], ['status' => true, 'reporting_method' => '3']], ]]) ->get();
And finally, for this specific case, you can use a $in operator:
self::select($selected_field) ->where('status', '=', true) ->where('reporting_method', 'in', [3, '3']) ->get();
Beta Was this translation helpful? Give feedback.
All reactions
-
Ok Thanks, but i need to change my code at all orWhere Occurance.
Anyway, Thanks for the help
Beta Was this translation helpful? Give feedback.
All reactions
-
Beta Was this translation helpful? Give feedback.
All reactions
-
👍 1