Hi dev,
In Laravel 10, managing relationships between database tables becomes even more convenient. One powerful feature it offers is the ability to calculate the sum of a specific column in a related table. By leveraging Laravel's Eloquent ORM, developers can establish relationships between models using methods like hasMany and belongsTo. For example, if you have a "Blog" model connected to a "View" model, where each blog can have multiple views, you can define a one-to-many relationship between them. Through this relationship, Laravel provides an elegant way to calculate the sum of a column in the related "View" table, such as the total number of views for a specific blog.
When it comes to searching related data, Laravel 10 offers flexibility and convenience. Leveraging the power of Laravel's query builder methods, developers can construct search queries easily. Suppose you want to search for blogs based on their titles or other attributes while also calculating the sum of a column in the related table. In that case, you can combine search functionality with the relationship column sum feature. By using query builder methods like where and orWhere, you can construct search queries that retrieve blogs matching specific criteria.
Step 1: Download Laravel
Let us begin the tutorial by installing a new laravel application. if you have already created the project, then skip the following step.
composer create-project laravel/laravel example-app
Step 2: Database Configuration
You can use MAMP or XAMPP as a local web server, and insert the database name, username, and password in the .env file.
.env
DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=laravel_db
DB_USERNAME=root
DB_PASSWORD=
Step 3: Add Model and Migration
In this step, you have to generate model and migration files. It can be done by using a single command, hence executing the following command.
php artisan make:model Invoice -m
After running the suggested command, new model and migration files have been generated, and you will add values in these files to create a table in the database.
app/Models/Invoice.php
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Invoice extends Model
{
use HasFactory;
/**
* The attributes that are mass assignable.
*
* @var array
*/
protected $table = 'invoices';
protected $fillable = [
'user_id',
'amount',
];
}
app/Models/User.php
<?php
namespace App\Models;
use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;
use Laravel\Sanctum\HasApiTokens;
use App\Models\Invoice;
class User extends Authenticatable
{
use HasApiTokens, HasFactory, Notifiable;
/**
* The attributes that are mass assignable.
*
* @var array
*/
protected $fillable = [
'name',
'email',
'password',
];
/**
* The attributes that should be hidden for serialization.
*
* @var array
*/
protected $hidden = [
'password',
'remember_token',
];
/**
* The attributes that should be cast.
*
* @var array
*/
protected $casts = [
'email_verified_at' => 'datetime',
];
public function invoices()
{
return $this->hasMany(Invoice::class,'user_id','id');
}
}
Now, get into the database/migrations/create_invoices_table.php, and you have to insert the table values into this migration file.
database/migrations/create_invoices_table.php
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateInvoicesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('invoices', function (Blueprint $table) {
$table->id();
$table->integer('user_id');
$table->integer('amount');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('invoices');
}
}
Now, get into the database/migrations/create_users_table.php, and you have to insert the table values into this migration file.
database/migrations/create_users_table.php
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateUsersTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->string('email')->unique();
$table->timestamp('email_verified_at')->nullable();
$table->string('password');
$table->rememberToken();
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('users');
}
}
Step 4: Run Database Migration
All the migration and model files have been updated now, and you have to execute the command to run migrations.
php artisan migrate
Step 5: Add Controller
Let us generate a new controller file using the following composer command.
php artisan make:controller UserController
Next, you have to open the app/Http/Controllers/UserController.php and update the file with the given code.
app/Http/Controllers/PostController.php
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\User;
use DataTables;
class UserController extends Controller
{
/**
* Write code on Method
*
* @return response()
*/
public function index(Request $request)
{
$input = $request->all();
if ($request->ajax()) {
$data = User::latest()->withSum(['invoices as filter_invoices'], 'amount');
if(!empty($request->search_filter)){
parse_str($input['search_filter'], $filter);
if(!empty($filter['amount_search'])){
$data = $data->havingRaw('COALESCE(filter_invoices,0)'.$filter['operator'].'?',[$filter['amount_search']]);
}
}
$data = $data->get();
return Datatables::of($data)
->addIndexColumn()
->addColumn('amount', function($row){
if($row->filter_invoices){
return $row->filter_invoices;
}else{
return "N/A";
}
})
->rawColumns(['amount'])
->make(true);
}
return view('users.index');
}
}
Step 6: Add Route
Now, you need to have a route defined in the routes/web.php file, and this route will be the endpoint or URL for generating the slug.
routes/web.php
<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\UserController;
/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| contains the "web" middleware group. Now create something great!
|
*/
Route::get('/', [UserController::class, 'index'])->name('users.index');
Open and update the below code in the resources/views/users/index.php file:
resources/views/users/index.php
<!DOCTYPE html>
<html>
<head>
<title>Laravel 10 Relationship Column Sum With Searching - nicesnippets.com</title>
<meta name="csrf-token" content="{{ csrf_token() }}">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.min.css" />
<link href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css" rel="stylesheet">
<link href="https://cdn.datatables.net/1.10.19/css/dataTables.bootstrap4.min.css" rel="stylesheet">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-validate/1.19.0/jquery.validate.js"></script>
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js"></script>
<script src="https://cdn.datatables.net/1.10.19/js/dataTables.bootstrap4.min.js"></script>
</head>
<body>
<div class="container">
<h3>Laravel 10 Relationship Column Sum With Searching - nicesnippets.com</h3>
<form name="search_filter">
@csrf
<div class="row mb-2">
<div class="col-sm-6 col-lg-3">
<label>Operator</label>
<select name="operator" class="form-control">
<option value="=">=</option>
<option value="<"><</option>
<option value=">">></option>
</select>
</div>
<div class="col-12 col-sm-6 col-lg-3">
<label>Amount</label>
<input type="text" name="amount_search" class="form-control">
</div>
</div>
<button type="button" data-toggle="tooltip" data-placement="top" data-original-title="Search" class="btn btn-icon btn-search btn-primary mb-1 waves-effect waves-light filter" style="margin-right: 4px;">Search</button><br>
</form>
<br>
<table class="table table-bordered data-table">
<thead>
<tr>
<th>No</th>
<th>Name</th>
<th>Email</th>
<th>Amount</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</body>
<script type="text/javascript">
$(function () {
var table = $('.data-table').DataTable({
processing: true,
serverSide: true,
ajax: {
url: "{{ route('users.index') }}",
data: function (d) {
d.search_filter = $("form[name='search_filter']").serialize();
}
},
columns: [
{data: 'DT_RowIndex', name: 'DT_RowIndex'},
{data: 'name', name: 'name'},
{data: 'email', name: 'email'},
{data: 'amount', name: 'amount'},
]
});
});
$('.filter').click(function(){
$('.data-table').DataTable().draw(true);
});
</script>
</html>
Run Laravel App:
All steps have been done, now you have to type the given command and hit enter to run the Laravel app:
php artisan serve
Now, you have to open the web browser, type the given URL and view the app output:
http://localhost:8000
Output:
I hope it can help you...
