Laravel provides us with an awesome eloquent relational aggregate method like withSum() which receives two parameters. One parameter is the relationship name and the other is an attribute name that you want to sum. Assume we have a comments table and there is a relationship between comments and posts like one post has many comments. Now, the comments field has a column name vote. Now you want to sum up every comment vote for every post. How you can do that?
To solve this problem, we can use withSum() eloquent aggregate method. If we write a query using withSum() then the resultant output of sum will look like that comments_sum_vote. So in this tutorial, I will show you how to use laravel 9 eloquent withsum.
Now, look at the example query of laravel withsum. Assume our comments table look like that:
<?php
use App\Models\Post;
use App\Models\User;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('comments', function (Blueprint $table) {
$table->id();
$table->foreignIdFor(Post::class)
->constrained()
->cascadeOnUpdate()
->cascadeOnDelete();
$table->foreignIdFor(User::class)
->constrained()
->cascadeOnUpdate()
->cascadeOnDelete();
$table->string('comment');
$table->integer('vote')->default(0);
$table->boolean('is_approved')->default(false);
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('comments');
}
};
And now update your Post model like:
app|Models\Post.php
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Factories\HasFactory;
class Post extends Model
{
use HasFactory;
public function comments()
{
return $this->hasMany(Comment::class);
}
}
Now use that in a controller like this to get with sum laravel:
app/Http/Controllers/TutorialController.php
<?php
namespace App\Http\Controllers;
use App\Models\Post;
class TutorialController extends Controller
{
/**
* Tutorial from LARAVELIA
*/
public function index()
{
return Post::withSum('comments','vote')->get();
}
}
Now look at that output:
[
{
"id": 1,
"user_id": 1,
"title": "Sed perspiciatis totam est. Earum pariat",
"post_views": 43,
"is_published": 1,
"created_at": "2023-01-29T07:31:39.000000Z",
"updated_at": "2023-01-29T07:31:39.000000Z",
"comments_sum_vote": "36"
},
{
"id": 2,
"user_id": 2,
"title": "Quae excepturi commodi et enim ut. Nisi et incidunt",
"post_views": 25,
"is_published": 1,
"created_at": "2023-01-29T07:31:39.000000Z",
"updated_at": "2023-01-29T07:31:39.000000Z",
"comments_sum_vote": "30"
}
]
Read aslo: Laravel WithCount Select Query Example
Conclusion
In this with sum laravel tutorial, I have tried my best to let you know laravel withsum. Hope now after completing this laravel 9 eloquent withsum example tutorial, you will know laravel eloquent withsum.
