CODEWITHSUNDEEP
laravellaravel-5
mana
mana

Reputation: 1239

Laravel fetch raw query result

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?

Upvotes: 0

Views: 6399

Answers (5)

mana
mana

Reputation: 1239

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.

Upvotes: 1

sumitpawar1991
sumitpawar1991

Reputation: 21

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();

Upvotes: 0

ascsoftw
ascsoftw

Reputation: 3476

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);

Upvotes: 0

Davit Zeynalyan
Davit Zeynalyan

Reputation: 8618

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);

Upvotes: 1

meph
meph

Reputation: 209

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.

Upvotes: 2

Related Questions