Laravel Query Collection, Just take a look

Laravel Query Collections

(Below Queries Based on above table design, Table Name "test")

[Ex # 1]

$data = test::all();

[Ex # 2]

$data = test::all()->toArray();

[Ex # 3]

$data = test::all()->toJson();

[Ex # 4]

$data = DB::table('test')->get()->toArray();

[Ex # 5]

$data = DB::table('test')->get()->toJson();

[Ex # 6]

$data = test::find(1); // if not found array return null

[Ex # 7]

$data = test::find([1,5,4]);

[Ex # 8]

$data = test::find(1)->item;

[Ex # 9]

$data = test::findOrFail(2); //if fail show 404

[Ex # 10]

$data = test::where('price','>','5')->get();

[Ex # 11]

$data = test::where('price','>','5')->take(1)->get(); // take as limit

[Ex # 12]

$data = test::all()->count(); // return non array, only value

[Ex # 13]

$data = test::where('price','>','15')->count();

[Ex # 14]

$data = test::find(1);

$data->item = 'Item 100';

$data->save(); // that's mean update & return 1

[Ex # 15]

$data = new test();

$data->item = 'Item 1000';

$data->price = '1000';

$data->qty = '1000';

$data->save(); // that's mean insert new record & return 1

[Ex # 16]

$data = test::all()->max('price'); // return value not array

[Ex # 17]

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

[Ex # 18]

$data = DB::table('test')->count(); // return only value

[Ex # 19]

$data = DB::table('test')->max('price'); // return only value

[Ex # 20]

$data = DB::table('test')->min('price'); // return only value

[Ex # 21]

$data = DB::table('test')->sum('price'); // return only value

[Ex # 22]

$data = DB::table('test')->avg('price'); // return only value

[Ex # 23]

$data = DB::table('test')->first();

[Ex # 24]

$data = DB::table('test')->get()->first();

[Ex # 25]

$data = DB::table('test')->get()->take(2);

[Ex # 26]

$data = DB::table('test')->pluck('item'); // return all item name in array

[Ex # 27]

$data = DB::table('test')->pluck('price','item'); // return key=>value array

[Ex # 28]

$data = DB::table('test')->select('item','qty')->get();

[Ex # 29]

$data = DB::table('test')->max('price');

[Ex # 30]

$data = DB::table('test')->distinct()->get(); // return distinct rows

[Ex # 31]

$data = DB::table('test')->select('item as Item_Name','qty as Item_QTY')->get(); // field naming

[Ex # 32]

$data = DB::table('test')->whereBetween('qty',[8,20])->get();

[Ex # 33]

$data = DB::table('test')->whereNotBetween('price',[50,200])->get();

[Ex # 34]

$data = DB::table('test')->whereIn('id',[2,4,6])->get();

[Ex # 35]

$data = DB::table('test')->whereNotIn('id',[2,4,6])->get();

[Ex # 36]

$data = DB::table('test')->whereQty('10')->get(); // join table field with where clause

[Ex # 37]

$data = DB::table('test')->whereIdAndQty('3','10')->get();

[Ex # 38]

$data = DB::table('test')->whereIdOrQty('3','5')->get();

[Ex # 39]

$data = DB::table('test')->skip(3)->take(4)->get(); // skip first 3 row and take 4 rows

[Ex # 40]

$data = DB::table('test')->orderByRaw('RAND()')->take(2)->select('item')->get(); // get random items

[Ex # 41]

$data = DB::table('test')->orderByRaw('RAND()')->take(2)->get(); // get random rows

[Ex # 42]

$data = DB::table('test')->insert(['item'=>'Item 2000','price'=>'2000','qty'=>'20']); // return true

[Ex # 43]

$data = DB::table('test')->insertGetId(['item'=>'Item 5000','price'=>'5000','qty'=>'50']); // return insert id

[Ex # 44]

$data = DB::table('test')->increment('qty'); // increment 1 with all qty

[Ex # 45]

$data = DB::table('test')->where('id','=',3)->increment('qty',10); // increment qty - 10 where id = 3

[Ex # 46]

$data = DB::table('test')->where('id','=',5)->decrement('qty','4');

[Ex # 47]

$data = DB::table('test')->delete(); // delete all , return how many row deleted

[Ex # 48]

$data = DB::table('test')->where('id','=',3)->delete();

[Ex # 49]

$data = DB::table('test')->truncate();

50+ Useful Laravel Quries (Part - 3)

18. LARAVEL MAX QUERY

Using Model (way1)
$data = exampleModel::all()->max('salary');
dd($data);

Using Model (way2)
$data = exampleModel::get()->max('salary');
dd($data);

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

Example #1 
$data = DB::select(DB::raw('select * from example_table where id = (select max(`id`) from example_table)'));
dd($data);

Example #2 
$data = exampleModel::whereRaw('id = (select max(`id`) from example_table)')->get();
dd($data);

Example #3 
$data = DB::table('example_table')->find(DB::table('example_table')->max('id'));
dd($data);

Example #4 
$data = exampleModel::find(DB::table('example_table')->max('id'));
dd($data);


19. LARAVEL MIN QUERY

Using Model (way1)
$data = exampleModel::all()->min('salary');
dd($data);

Using Model (way2)
$data = exampleModel::get()->min('salary');
dd($data);

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

Example #1 
$data = DB::select(DB::raw('select * from example_table where id = (select min(`id`) from example_table)'));
dd($data);

Example #2 
$data = exampleModel::whereRaw('id = (select min(`id`) from example_table)')->get();
dd($data);

Example #3 
$data = DB::table('example_table')->find(DB::table('example_table')->min('id'));
dd($data);

Example #4 
$data = exampleModel::find(DB::table('example_table')->min('id'));
dd($data);

Example #5 
$data = exampleModel::where('id', '>', DB::table('example_table')->min('id'))
->where('id', '<', DB::table('example_table')->max('id'))
        ->get();
dd($data);


20. LARAVEL AVG QUERY

Using Model (way1)
$data = exampleModel::all()->avg('salary');
dd($data);

Using Model (way2)
$data = exampleModel::get()->avg('salary');
dd($data);

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


21. LARAVEL SUM QUERY

Using Model (way1)
$data = exampleModel::all()->sum('salary');
dd($data);

Using Model (way2)
$data = exampleModel::get()->sum('salary');
dd($data);

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

50+ Useful Laravel Quries (Part - 2)

17. HOW TO PRINT RAW SQL QUERY IN LARAVEL

Example #1
$sql = exampleModel::where('name', '=', 'Arindam Roy')
->orWhere('age', '>=', '20')->toSql();

echo $sql;

OUTPUT :

"select * from `example_table` where `name` = ? or `age` >= ?"

---

Example #2 (Grouping) (Nested where grouping)
$sql = DB::table('example_table');

$sql = $sql->where( function($sql) {

        $sql->where('name', 'like', '%ari%');
        $sql->where('age', '>', '30');
});
$sql = $sql->orWhere( function($sql) {

        $sql->where('salary', '<', '25000');
        $sql->where('age', '>', '30');
});
$sql = $sql->toSql();
echo $sql;

OUTPUT :
"select * from `example_table` where (`name` like ? and `age` > ?) or (`salary` < ? and `age` > ?)"


Example #3 (More nested query) 
$sql = DB::table('example_table');

$sql = $sql->where( function($sql) {

               $sql->where('name', 'like', '%ari%');
               $sql->where('age', '>', '30');

});

$sql = $sql->orWhere( function($sql) {

            $sql->where('salary', '<', '50000');
            $sql = $sql->where( function($sql) {

                     $sql = $sql->where('id', '>', '2');
                     $sql = $sql->orWhere('id', '=', '5');
      });

});

$sql = $sql->toSql();
echo $sql;



OUTPUT :
"select * from `example_table` where (`name` like ? and `age` > ?) or (`salary` < ? and (`id` > ? or `id` = ?))"

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