I have table name payments in this table each row has column named paid which is boolean. When I try to get separate list of paid and unpaid rows based on this paid column it just return all rows regardless.

Code

Get unpaid rows

$purchases = Payment::wherePaid(false)
    ->where('employee_id', $user->id)
    ->orWhere('employer_id', $user->id)->with([
        'employer',
        'employee' => function($q) {
            $q->select(['id', 'first_name', 'last_name']);
        },
        'template',
        'training',
        'contract',
    ])->get();

Get paid rows

$purchases = Payment::wherePaid(true)
    ->where('employee_id', $user->id)
    ->orWhere('employer_id', $user->id)->with([
        'employer',
        'employee' => function($q) {
            $q->select(['id', 'first_name', 'last_name']);
        },
        'template',
        'training',
        'contract',
    ])->get();

Model

protected $casts = [
  'paid' => 'boolean',
];

In both queries I am getting array of 4 which is include 3 unpaid and 1 paid row.

Update

Schema

public function up()
{
    Schema::create('payments', function (Blueprint $table) {
        $table->id();
        $table->unsignedBigInteger('employer_id')->nullable();
        $table->unsignedBigInteger('employee_id')->nullable();
        $table->unsignedBigInteger('template_id')->nullable();
        $table->unsignedBigInteger('contract_id')->nullable();
        $table->unsignedBigInteger('training_id')->nullable();
        $table->string('duration')->nullable();
        $table->string('amount');
        $table->boolean('paid')->default(false);
        $table->string('booking_id')->nullable();
        $table->string('invoice_no');
        $table->timestamps();
    });
    Schema::table('payments', function (Blueprint $table) {
        $table->foreign('employer_id')->references('id')->on('users')->onDelete('cascade');
        $table->foreign('employee_id')->references('id')->on('users')->onDelete('cascade');
        $table->foreign('template_id')->references('id')->on('contract_templates')->onDelete('cascade');
        $table->foreign('contract_id')->references('id')->on('contracts')->onDelete('cascade');
        $table->foreign('training_id')->references('id')->on('trainings')->onDelete('cascade');
    });
}

>Solution :

I may be wrong, but I think your issue is that you are asking for paid = false AND employee_id = ID OR employer_id = ID. I think MySQL is thinking:

(paid = false AND employee_id = ID) OR (employer_id = ID)

So, try this query instead:

$purchases = Payment::wherePaid((false or true))
    ->where(function ($query) use ($user) {
        return $query->where('employee_id', $user->id)
            ->orWhere('employer_id', $user->id)
    })
    ->with([
        'employer',
        'employee' => function($query) {
            return $query->select(['id', 'first_name', 'last_name']);
        },
        'template',
        'training',
        'contract',
    ])
    ->get();

That will change the query to:

(paid = false or true) AND (employee_id = ID OR employer_id = ID)