I don't want laravel to format my query result to an array or object ..etc. All I want, is to run the result set from database and then I will manually do the fetch myself in my custom code.
At the moment, I ran my select query and get my result in an array. The reasons for that, because the result is huge and I want to stream it directly to API.
$result = self::$db->select('select * from customer');
How can I tell laravel, to return my query result set without any format at all?
You can use DB:Raw like:
$results = DB::table('users')->select(DB::raw("*"))->get()
Or
$results = DB::select('select * from users where id = ?', [1]);
These two will return a neat object without any casts or relations etc. You can also make any object or array your API need by simple eloquent models by the way. Please explain more about data type you wanna extract from model query.
You must be use ->toSql() or ->dd()
Exapmle
Customer::toSql(); // select * from `customer`
if you want some condition
$query = Customer::where(`some conditions`);
$sql = $query->toSql();
$bindings = $query->getBindings();
$sql = str_replace('?', '%s', $sql);
$sql = sprintf($sql, ...$bindings);
Thanks everyone, I end up writing a raw function to query the data I want from database.
public static function dataStreamJSON($stmt, $headers)
{
return Response::stream(function() use ($stmt){
$conn = self::getConnection();
$result = sqlsrv_query($conn, "exec $stmt");
echo '
{
"Customers": {
"Customer": [';
$counter = 0;
while($customer = sqlsrv_fetch_object($result)) {
if($counter !== 0){
echo ",";
}
$counter++;
$row = [
'Firstname' => $customer->Firstname,
'Lastname' => $customer->Lastname,
...
];
echo json_encode($row);
unset($row);
unset($customer);
}
echo ']
}
}';
@sqlsrv_free_stmt($result);
@sqlsrv_close($conn);
}, 200, $headers);
}
The purpose of this code is to stream the data out to JSON format on browser without store the data in any variable, which will caused “out of memory” error.
I managed to stream 700MB of JSON data to the browser without any error. With this code, you will never run into “out of memory” error.
Best way to test this, is to use CURL to access your API and download the data to a JSON file. If you open on browser, it will freeze your screen because browser can't handle large data.
You can use toArray() or toJson() methods like below:
$array = Customer::all()->toArray();
$json = Customer::all()->toJson();
echo '<pre>';
print_r($array);
print_r($json);
If you want to run raw SQL Queries, you can do as below
$users = DB::select('select * from users where 1');
echo '<pre>';
print_r($users);
You can use 1) query builder way:-
DB::table('your_table_name)->select('your_col_names')->get();
eg:- DB::table('shop')->select('product_id','product_name')->get();
2) use laravel Raw
$orders = DB::table('orders')->selectRaw('price * ? as price_with_tax', [1.0825])->get();
3) for select raw
$product_count = DB::table('product')->select(DB::raw('count(*) as total_product_count'))->where('status',1)->get();
