Laravel boolean returns "1"/"0" instead of true/false in query

Question

I have a query that returns a boolean field (by the way, I'm on an SQL Server) on my REST API developed with Laravel and a few days ago it was returning the value as true/false, but now it returns that boolean value as String: "1"/"0".

Any idea on what could it be?

I tried using casting on my Model like this:

 protected $casts = [
    'status' => 'boolean',
 ];

This is my query:

return DB::table('dbo.visits as V')
        ->leftJoin('dbo.reports AS R', 'R.id_visit', '=', 'V.id')
        ->select('R.id','R.status'); // status is the boolean

Thanks in advance.

SQL Server doesn't have a `boolean` data type, so you can't have a query that returns one. If you want to store the values `TRUE`/`FALSE` you would have to use a `varchar(5)` and the values `'true'` and `'false'`. Most people, however, use a `bit`, which allows the values `0` and `1` (and `NULL`), however, it does not *act* like a boolean. — Thom A, Nov 19 '19 at 16:43
*"Yes, it's stored as a bit "* Well that's why you're getting `0` or `1`; because that's the value of the column. A `bit` isn't a `boolean`. The 2 are different data types. — Thom A, Nov 19 '19 at 16:45
Consider to use Eloquent, where you can cast the attribute to boolean https://laravel.com/docs/6.x/eloquent-mutators#attribute-casting — porloscerros Ψ, Nov 19 '19 at 16:46
@porloscerrosΨ I forgot to mention that I've already tried that but didn't work — guillefix, Nov 19 '19 at 16:47
I think you need to use Eloquent model for cast to work. Right now you are using raw query to select directly from table. — user3532758, Nov 19 '19 at 16:52
Oh, yes, you have to perform the query by your eloquent Model, the cast will not work by DB Query Builder. Also, you can try with an accesor. Something like `public function getStatusAttribute($value) { $value ? true : false; }` — porloscerros Ψ, Nov 19 '19 at 16:56
@porloscerrosΨ wouldn't the same problem occur? Will it work on DB query? — user3532758, Nov 19 '19 at 16:57
@user3532758 Yes, for the two options I mentioned, the query should be done from the Eloquent Model. I think the cast should work and it is the specific tool for that. I only mentioned the accessor because OP mentioned that cast doesn't work. — porloscerros Ψ, Nov 19 '19 at 17:01

score 19 · Accepted Answer · answered Nov 19 '19 at 16:56

When you're defining the following:

protected $casts = [
  'status' => 'boolean',
];

You're defining it on the model. However, when you initiate your query using DB::table(), you're not using the model, so the binding doesn't actually apply. Initiate your query using your model:

return Visit::leftJoin('dbo.reports AS R', 'R.id_visit', '=', 'dbo.visits.id')
->select('R.id','R.status'); // status is the boolean

Note: Had to adjust query to dbo.visits.id from V.id due to aliasing not being available at that point.

score 2 · Answer 2 · answered Nov 19 '19 at 19:01

2

Defining casts is working when you are using Eloquent models. In code you provided you use query builder and then Eloquent models are not used at all, so no casts are done.

answered Nov 19 '19 at 19:01

Marcin Nabiałek

109,655
42
258
291

score 0 · Answer 3 · answered Nov 02 '21 at 03:51

What you would like to do is add this function to your model. Every time you use your model to retrieve data, this function matching the attribute name will be called to convert your raw data from the database to your desired format.

public function getStatusAttribute($value)
{
        return $value ? 'Yes' : 'No';
}

score 0 · Answer 4 · answered Sep 23 '22 at 16:31

You could use Eloquent: API Resources

/*Controller*/
$sel_visit= Visit::leftJoin('dbo.reports AS R', 'R.id_visit', '=', 'dbo.visits.id')->select('R.id','R.status')->get(); // status is the boolean
VisitResource::collection($sel_visit);

Then using a API Resources

public function toArray($request)
    {

        if($this->status==1) { $status=true;} else {$status=false;}
        
        return [
            'id' => $this->id,
            'status' => $status
        ];
    }

Laravel boolean returns "1"/"0" instead of true/false in query

4 Answers4