CODEWITHSUNDEEP
sqllaravel
akash
akash

Reputation: 51

how to write sql query in laravel

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

Upvotes: 1

Views: 14672

Answers (3)

Tousif Ahmed
Tousif Ahmed

Reputation: 1082

This 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]);
 }
}

Upvotes: 2

Siddharth
Siddharth

Reputation: 1769

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

Upvotes: 2

Carlos
Carlos

Reputation: 1321

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

Laravel Doc for Raw Queries

Also :

$someVariable = Input::get("some_variable");

$results = DB::select( DB::raw("SELECT * FROM some_table WHERE some_col = :somevariable"), array('somevariable' => $someVariable));

http://fideloper.com/laravel-raw-queries

Upvotes: 0

Related Questions