Create | Update Data Using Eloquent Request

• Create data using eloquent request

  public function insert(ModelRequest $request)
  {
    Model::create($request->all());
  }

• Update Data using eloquent request

  public function update(ModelRequest $request, Model $modelInstance)
  {
    $modelInstance->fill($request->all())->save();
  }

Update Existing Column

• Setting email to unique

  public function up()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->unique('email', 'users_email_uniq');
        });
    }
    
  public function down()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->dropUnique('users_email_uniq');
        });
    }

• Adding new Column to a table with a default value

  public function up()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->string('phone')->after('password')->default(null);
        });
    }
    
  public function down()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->dropColumn('phone');
        });
    }

• Modify the column to make it nullable

  public function up()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->time('phone')->nullable()->change();
        });
    }

• Adding new Column to a table after a specific column

  public function up()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->string('count')->default(0)->after('column_name');
        });
    }
    
  public function down()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->dropColumn('count');
        });
    }

Drop Column(s) from table using migration

• Drop a column

  public function up()
    {
        Schema::table('table_name', function (Blueprint $table) {
            $table->dropColumn('column_name');
        });
    }
    
  public function down()
    {
    }

• Drop multiple columns

  public function up()
    {
        Schema::table('table_name', function (Blueprint $table) {
            $table->dropColumn(['column_name1', 'column_name2', 'column_name3']);
        });
    }
    
  public function down()
    {
    }

• Drop column if exists

  public function up()
    {
        if (Schema::hasColumn('table_name', 'column_name')){
            Schema::table('table_name', function (Blueprint $table) {
                $table->dropColumn('column_name');
            });
        }
    }
    
  public function down()
    {
    }

• Drop multiple columns if exists using foreach

  public function up()
    {
        $table_name = 'table_name';
        $columns = ['column_name1', 'column_name2', 'column_name3'];
        foreach ($columns as $col) {
            if (Schema::hasColumn($table_name, $col)) {
                Schema::table($table_name, function (Blueprint $table) use ($col) {
                    $table->dropColumn($col);
                });
            }
        }
    }
    
  public function down()
    {
    }

Drop Foreign key Constraints from table using migration

• Drop the foreign key constraints, but it will keep the column in the table. To remove the column too, please check this section

  public function up()
    {
        Schema::table('table_name', function (Blueprint $table) {
            $table->dropForeign(['foreign_key_id']);
        });
    }
    
  public function down()
    {
    }

Retrieve Single Model Instance

• Retrieve a model by its primary key or id

  $instance = ModelName::find(1);

• Retrieve the first model data matching the condition

  $instance = ModelName::where('active', 1)->first();

  Or, an alternative to above statement by using firstWhere. It do the same (Matching the condition).

  $instance = ModelName::firstWhere('active', 1);

• Throw an exception if a instance of model is not found

  $instance = ModelName::findOrFail(1);

  Or,

  $instance = ModelName::where('age', '>', 30)->firstOrFail();

WhereNotIn Clause Query

• Get Data Where Countries Not In North America, South America, Antarctica, Europe continents.

  Country::whereNotIn('continent', [ 'North America', 'South America', 'Antarctica', 'Europe' ])->get();

Fetch a single column value from the table

• The value() method in Eloquent to fetch a single column from the DB

  Model::where('id', 1)->value('name');

Date columns

• Get Data Between Two Dates

  User::whereBetween('created_at', [$startDate, $endDate])->get();
  Or
  User::whereDate('created_at', '>=', $startDate)->whereDate('created_at', '<=', $endDate)->get();

Sum with Where & Group By

• Find the total sum of paid amount of an user based on payment methods

  Model::select("user_id", db::raw("SUM(paid_amount) AS amount"))->where('user_id', $user_id)
  	->groupBy('payment_method')->get();

Conditional Query | When Clause

• Filtering in where caluse if else condition

  Comment::select("*")
  	->when($request->has('comment_id'), function ($query) use ($request) {
       		$query->where('comment_id', $request->comment_id);
        })->get();

Full Text SEARCH over columns

• Put below statements inside public function up() of migration

  # Laravel doesn't support full text search migration
  
  // Single Column
  DB::statement('ALTER TABLE TABLE_NAME ADD FULLTEXT search_index(columnName)'); 
  // Multiple Column
  DB::statement('ALTER TABLE TABLE_NAME ADD FULLTEXT search_index(column1, column2)'); 

• Put below statements in public function down() of migration

  Schema::table('model_name', function($table) {
        $table->dropIndex('search_index');
    });

• Now write the eloquent statement in controller / service class / actions class.

  // Single Column
  ModelName::whereRaw('MATCH (columnName) AGAINST (?)' , array($request->search_text))->get();
  // Multiple Column
  ModelName::whereRaw('MATCH (column1, column2) AGAINST (?)' , array($request->search_text))->get();

JSON Where Clauses

• Find in Nested JSON Array with multiple Key userlist.json

  DB::table('my_table_name')
            ->whereJsonContains('column_name->data', ['first_name' => 'Michael', 'last_name' => Lawson])
            ->get();

• Find in Nested JSON Array with single Key userlist.json

  DB::table('my_table_name')
            ->whereJsonContains('column_name->data', ['email' => '[email protected]'])
            ->get();

• Find in JSON Key singleuser.json

  DB::table('my_table_name')
            ->whereJsonContains('column_name->data->email',  '[email protected]')
            ->get();

• Find in JSON vis LIKE Search singleuser.json

  ModelName::where('column_name->data->email', 'like', '%weaver@reqres%')
            ->get();