Laravel withSum Where Condition Query Example

Laravel provides us withMin, withMax, withAvg, withSum, and withExists methods. In the previous tutorial, I discussed how to use withSum in laravel. Now in this example, I will use a complex version of withsum like laravel withsum condition. That we will write query with withsum along with the condition. I will use laravel withsum where and laravel withsum callback to create this example tutorial.

Suppose 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 calculate the sum of 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 withsum callback in a proper way in laravel 9 version.

Now, look at the example query of withsum with condition laravel. Assume our comments table looks 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 laravel withsum where:

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' => fn ($query) => $query->where(
                            'is_approved', false
                        )
                    ],
                        '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: Sum Relational Model Column Using WithSum In Laravel

 

Conclusion

In this laravel withsum condition tutorial,  I have tried my best to let you know how to write a query with where condition using withsum in laravel 9. Hope now after completing this laravel withsum where example tutorial, you will know laravel withsum callback.