Laravel boolean returns "1"/"0" instead of true/false in query
When a Laravel application suddenly starts returning string values like "1" or "0" for a field that previously returned true or false, it usually points to a mismatch between how the database driver handles data types and how Eloquent expects them. In your specific case involving SQL Server, this behavior often stems from how the raw query interacts with Eloquent's type casting mechanisms.
The core issue lies in the distinction between executing a raw SQL query via DB::table() and using standard Eloquent model methods. When you run DB::table('dbo.visits as V')->leftJoin('dbo.reports AS R', 'R.id_visit', '=', 'V.id')->select('R.id','R.status'), you are bypassing the Eloquent model entirely for that specific request. While DB facade queries are powerful, they do not automatically apply the $casts property defined on your Model class unless you explicitly tell them to.
In SQL Server, boolean columns often map to the BIT data type. When retrieved via raw SQL or the DB facade without a model wrapper, the driver returns these as integers (1 or 0) rather than booleans. This is standard behavior for the Microsoft ODBC/ODBC drivers used by Laravel when connecting to SQL Server. The previous behavior where you saw true/false likely occurred because your application might have previously been using Eloquent's get() or find() methods, which trigger automatic type casting based on your Model definition.
To fix this while sticking with your current query structure, you need to instruct Laravel to cast the results before returning them. You can do this by appending a setClassCast method to the query builder instance. This allows you to leverage the same casting logic defined in your model without switching entirely to Eloquent syntax.
Here is how you can modify your query:
use Illuminate\Support\Facades\DB;
$result = DB::table('dbo.visits as V')
->leftJoin('dbo.reports AS R', 'R.id_visit', '=', 'V.id')
->select('R.id', 'R.status')
->setClassCast(['status' => 'boolean']); // Explicitly cast the status column
// Now $result->get() will return true/false for the status field
return response()->json($result->get()->toArray());
Alternatively, if you prefer to switch back to Eloquent models, which is often cleaner for complex joins, you should ensure your Model is properly configured. Define the $casts array in the model as you attempted:
protected $casts = [
'status' => 'boolean',
];
Then, instead of using DB::table, use the Model's query builder or retrieve via get():
use App\Models\Report; // Adjust namespace accordingly
return Report::join('visits', 'reports.id_visit', '=', 'visits.id')
->select('reports.*')
->get();
It is important to note that when using Eloquent's get(), the $casts property only applies if you are retrieving a collection of models. If you are returning raw columns from a join using select() on a table alias, the casting won't happen automatically unless you use setClassCast or map the values in your controller logic.
Another potential pitfall is ensuring that the column name in your select statement matches exactly what is expected in the cast definition. If you select an aliased column like alias.status, the cast might not apply unless you alias it correctly or handle the mapping manually. For instance, if the raw query returns a column named status but the model expects a different property name, you might need to adjust the casting key or use pluck() and map the values afterward.
If you continue to face issues with type consistency across your API, consider centralizing your data transformation logic in a service class or directly within the controller. This ensures that regardless of whether you use raw queries or Eloquent, the JSON response always conforms to the expected schema. For teams managing multiple environments where SQL Server behavior can vary due to driver versions, maintaining explicit casting rules helps prevent these subtle regressions from appearing sporadically in production.