hello friends i have this query that runs using sql editor but i have no idea how to write this query using laravel.please help me out.
SELECT
date,
memo,
(COALESCE(CASE WHEN drcr = 'dr' THEN amount END,0)) total_debits,
(COALESCE(CASE WHEN drcr = 'cr' THEN amount END,0)) total_credits,
@b := @b + (COALESCE(CASE WHEN drcr = 'cr' THEN amount END,0)) - (COALESCE(CASE WHEN drcr = 'dr' THEN amount END,0)) balance
FROM
(SELECT @b := 0.0) AS dummy
CROSS JOIN
tbl_bankrecords
ORDER BY
date
You may try this:
Model::select(DB::raw('SELECT
date
,memo
,(COALESCE(CASE WHEN drcr = 'dr' THEN amount END,0)) total_debits
,(COALESCE(CASE WHEN drcr = 'cr' THEN amount END,0)) total_credits,
@b := @b + (COALESCE(CASE WHEN drcr = 'cr' THEN amount END,0)) - (COALESCE(CASE WHEN drcr = 'dr' THEN amount END,0)) balance'))->get();
Model::select(DB::raw('PUT YOUR RAW QUERY HERE'))->get();. I'm not sure of it but this should workThis should be helpful for you, Anyhow below is the sample code from Laravel documentation.
<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;
class UserController extends Controller
{
/**
* Show a list of all of the application's users.
*
* @return Response
*/
public function index()
{
$users = DB::select('select * from users where active = ?', [1]);
return view('user.index', ['users' => $users]);
}
}
Raw Expressions
Sometimes you may need to use a raw expression in a query. These expressions will be injected into the query as strings, so be careful not to create any SQL injection points! To create a raw expression, you may use the DB::raw method:
$users = DB::table('users')
->select(DB::raw('count(*) as user_count, status'))
->where('status', '<>', 1)
->groupBy('status')
->get();
Also :
$someVariable = Input::get("some_variable");
$results = DB::select( DB::raw("SELECT * FROM some_table WHERE some_col = :somevariable"), array('somevariable' => $someVariable));
