Laravel Raw Select Returning Repeated Values

Question

I have the following test method in my model, it gets the length of a json column in a row:

public function test(){
    return $this->select(DB::raw('JSON_LENGTH((SELECT comments FROM lab_evals WHERE id='.$this->id.'))'))->get();
    }

In tinker it returns this:

= Illuminate\Database\Eloquent\Collection {#7430
    all: [
      App\Models\LabEval {#7429
        JSON_LENGTH((SELECT comments FROM lab_evals WHERE id =50)): 2,
      },
      App\Models\LabEval {#7431
        JSON_LENGTH((SELECT comments FROM lab_evals WHERE id =50)): 2,
      },
    ],
  }

Not really a big deal since I can just call first(), but I'm wondering why there are two objects in the collection instead of one.

I would expect this:

= Illuminate\Database\Eloquent\Collection {#7430
    all: [
      App\Models\LabEval {#7429
        JSON_LENGTH((SELECT comments FROM lab_evals WHERE id =50)): 2,
      },
    ],
  }

Edit: Here's the raw SQL that Laravel generates for this query for some model.

"select JSON_LENGTH((SELECT comments FROM lab_evals WHERE id=50)) from `lab_evals`"

The result I'm getting would make sense.

This is the proper raw SQL, but still not sure how I could translate this to a Laravel DB query builder.

(SELECT JSON_LENGTH((SELECT comments from lab_evals WHERE id=50))); 

Edit 2:

I ended up just doing this, marking as solved:

DB::select('SELECT JSON_LENGTH((SELECT comments from lab_evals WHERE id='.$this->id.'))')

Answer 1

Move your where out of the select, right now it's not applied to the whole query, only your specific select. Something like this should do the trick.

return $this->select(DB::raw('JSON_LENGTH((SELECT comments FROM lab_evals))'))->where('id', $this->id)->get();