50+ Useful Laravel Quries

MYSQL TABLE DESIGN

(click on this image , get full view to better understand)

MODEL OF THE TABLE ( model name : exampleModel )

<?php

namespace app\Models;

use Illuminate\Database\Eloquent\Model;

class  exampleModel extends Model {

    protected $table = 'example_table';

    protected $primarykey = 'id';

}

?>

CONTROLLER CODE ( controller name : exampleController )

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use App\Models\exampleModel;

use DB;

class exampleController extends Controller

{

    public function QueryTest() {

     /*** 'QueryTest()' is my method name ***/

    /*** You should write all below queries within this block ***/ 
    /*** So, lets start ***/

    }

}

?>

1. INSERT QUERY : ( ELOQUENT ORM )

$exampleModel = new exampleModel;

$exampleModel->name = "Arindam Roy";
$exampleModel->age = "31";
$exampleModel->salary = "55000";

$exampleModel->save(); 

2. INSERT QUERY : ( DB QUERY or ACTIVE RECORD )

DB::table('example_table')->insert([ 'name' => 'Sourav Roy', 'age' => '23', 'salary' => '20000']);

3. INSERT QUERY : ( RAW QUERY )

DB::insert('insert into example_table (name, age, salary) values (?, ?, ?)', ['Puja Roy', '18', '12000']);

4. INSERT QUERY : ( RAW QUERY ANOTHER WAY)

DB::insert(DB::raw("INSERT INTO `example_table`(`name`, `age`, `salary`) VALUES ('Ritu Roy', '30', '25000')"));

5. GET LAST INSERT ID : ( ELOQUENT ORM )

$exampleModel = new exampleModel;

$exampleModel->name = "Arindam Roy";
$exampleModel->age = "31";
$exampleModel->salary = "55000";

$exampleModel->save(); 

echo "Last Insert Id : ".$exampleModel->id;

6. GET LAST INSERT ID : ( DB QUERY )

$last_id = DB::table('example_table')->insertGetId([ 'name' => 'Sourav Roy', 'age' => '23', 'salary' => '20000']);

echo $last_id;

7. GET LAST INSERT ID : ( USING MODEL )

$last_id = exampleModel::insertGetId([
 
      'name' => 'Rahul Dutta',
      'age' => '12',
      'salary' => '5000'
]);

echo $last_id;

8. GET ALL RECORD FROM TABLE : ( USING MODEL ) way1

$data = exampleModel::all();
dd($data);

9. GET ALL RECORD FROM TABLE : ( USING MODEL ) way2

$data = exampleModel::get();
dd($data);

10. GET ALL RECORD FROM TABLE : ( USING DB QUERY )

$data = DB::table('example_table')->get();
dd($data);

11. GET ALL RECORD FROM TABLE : ( USING RAW QUERY )

$data = DB::select('select * from example_table');
dd($data);

12. GET ALL RECORD IN ARRAY FORMAT

Using Model
$data = exampleModel::all()->toArray();
dd($data);

Using DB Query
$data = DB::table('example_table')->get()->toArray();
dd($data);

13. GET ALL RECORD IN JSON FORMAT

Using Model
$data = exampleModel::all()->toJson();
dd($data);

Using DB Query
$data = DB::table('example_table')->get()->toJson();
dd($data);

14. COUNT TOTAL RECORD IN A TABLE

Using Model (way 1)
$total_records = exampleModel::count();
echo $total_records;

Using Model (way 2)
$total_records = exampleModel::all()->count();
echo $total_records;

Using Model (way 3)
$total_records = exampleModel::get()->count();
echo $total_records;

Using DB Query (way 1)
$total_records = DB::table('example_table')->count();
echo $total_records;

Using DB Query (way 2)
$total_records = DB::table('example_table')->get()->count();
echo $total_records;

15. SEARCH OR FIND RECORDS FROM TABLE

Way #1 (Pass id, ex 1 is a id of the table)
$data = exampleModel::find(1);
dd($data);

Way #2 (Pass multiple ids an array, ex 1,2,3,4 is a id of the table)
$data = exampleModel::find([1,2,3,4]);
dd($data);

Way #3 (Pass id, if fail show 404)
$data = exampleModel::findOrFail(1);
dd($data);

## After Search Get Records
$data = exampleModel::find(1);
echo "Name :".$data->name;
echo "Age :".$data->age;
echo "Salary :".$data->salary;

16. AND , OR, WHERE CLAUSE IN LARAVEL

Example #1 (Using Model)
$data = exampleModel::where('id', '=', '2')->get();
dd($data);

Example #2 (Using Model with AND i.e multiple where)
$data = exampleModel::where('id', '=', '2')
         ->where('age', '=', '30')
         ->get();

dd($data);

Example #3 (Using Model with OR i.e multiple orWhere)
$data = exampleModel::where('name', 'like', '%arindam%')
         ->orWhere('age', '>=', '30')
         ->orWhere('salary', '>', '25000')
         ->get();

dd($data);

Example #4 (Using DB Query)
$data = DB::table('example_table')->where('id', '=', '2')->get();
dd($data);

Example #2 (Using DB Query with AND i.e multiple where)
$data = DB::table('example_table')->where('id', '=', '2')
         ->where('age', '=', '30')
         ->get();

dd($data);

Example #3 (Using DB Query with OR i.e multiple orWhere)
$data = DB::table('example_table')->where('name', 'like', '%arindam%')
         ->orWhere('age', '>=', '30')
         ->orWhere('salary', '>', '25000')
         ->get();

dd($data);