skip to Main Content

I found a weird situation when nesting in a query a where with a orWhereNull.
The result is returning rows with values that are neither null nor correct

This is the code:

$query = Document::where("active", true)->where("type_id", $document_type->id);
if($selected) {
    $query->where(function($q) use($selected) {
        return $q->where("origen_id", $selected->id)
            ->orWhereNull("origen_id");
    });
}
$result = $query->orderBy("origen_id")->get();

When debuggin, $query->toSql() returns:
select * from documents where active = ? and type_id = ? and (origen_id = ? or origen_id is null)

The values of the wheres are correct:
wheres1
wheres2

when I run this query, it returns only one result, but the $query->get() is returning 2, one of them, with a origen_id diferent than $selected->id (and not null)

This is a really weird thing, and I’m not sure how to get the espected results, bcose the generated query is OK

2

Answers


  1. Chosen as BEST ANSWER

    So.... this was a 2 part problem. From testing reported a problem (a non-existent one), so when y run the query and return one value (instead of the 2 showed in the app) I assume that there was some kind of problem somewere.

    Remeber, always check and double-check to witch DataBase you are pointing.... really, you never know.


  2. The issue you’re encountering with the generated SQL query seems to be related to the missing spaces in the SQL string. This can happen if there’s a problem with how the query is being built in Laravel.

    Here is the corrected code snippet :

    $query = Document::where("active", true)->where("type_id", $document_type->id);
    
    
    if($selected) {
    $query->where(function($q) use($selected) {
        $q->where("origen_id", $selected->id)
          ->orWhereNull("origen_id");
    });}
    
    $result = $query->orderBy("origen_id")->get();
    // Debugging
    

    dd($query->toSql(), $query->getBindings());

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search