Laravel query scope with multiple where clauses returns incorrect data

Question

I have below table consols:

Schema::create('consols', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->double('current_weight', 8, 3)->default(0);
    $table->double('current_weight', 8, 3)->default(0);
    $table->double('final_weight', 8, 3)->default(0);
    $table->double('final_cbm', 8, 3)->default(0);    
    $table->timestamp('finals_sent', 0)->nullable();
});

Which contain an entry like below:

id | current_weight | current_cbm | final_weight | final_cbm | finals_sent         |
-----------------------------------------------------------------------------------|
1  | 45.000         | 1.000       | 200.000      | 10.000    | 2019-09-26 10:03:59 |

I have written a scope on my model Consol, to filter the entries, where: finals_sent is not null, current_weight > final_weight OR current_cbm > final_cbm:

public function scopeOfCurrentGreaterThanFinals($query)
{
    return $query->whereNotNull('finals_sent')->where(function ($query) {
        $query->where('current_weight', '>', 'final_weight')
            ->orWhere('current_cbm', '>', 'final_cbm');
    });

}

When I use it and dump it out - I am expecting to see no results:

$consols = Consol::ofCurrentGreaterThanFinals()
           ->orderBy('awb', 'DESC')
           ->get()
           ->toArray();

However, the entry above is still being shown:

array:1 [▼
  0 => array:24 [▼
    "id" => 1
    "current_weight" => 45.0
    "current_cbm" => 1.0
    "final_weight" => 200.0
    "final_cbm" => 10.0
    "finals_sent" => "2019-09-26 10:03:59"
  ]
]

I am not sure what I am doing wrong? I think I cleary states that I only want to see the results, where:

current_weight > final_weight or
current_cbm > current_weight and
finals_sent is not null

And above entry does not fit this, because the current_weight and current_cbm is clearly not higher than the final_weight and final_cbm.

What am I doing wrong?

what about your offWeek scope the Finals scope seems fine

mrhn
– mrhn

2019-09-26 08:43:58 +00:00
Commented Sep 26, 2019 at 8:43 — mrhn
– mrhn, Commented Sep 26, 2019 at 8:43
Removed that from the question.

oliverbj
– oliverbj

2019-09-26 08:46:27 +00:00
Commented Sep 26, 2019 at 8:46 — oliverbj
– oliverbj, Commented Sep 26, 2019 at 8:46

Dino Numić · Accepted Answer · 2019-09-26 09:16:06Z

1

Ah ok, here is the issue. In order to compare the two columns you have to use whereRaw() as where for the second parameter expects a value, not a column. So this should work, I tested with your database and values.

return $query->whereNotNull('finals_sent')->where(function ($q) {
            $q->whereRaw('current_weight > final_weight')
                ->orWhereRaw('current_cbm > final_cbm');
        });

edited Sep 26, 2019 at 9:16

answered Sep 26, 2019 at 8:43

Dino Numić

1,4722 gold badges11 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

oliverbj Over a year ago

I just updated the question. I actually did intend to get the results if current_weight or current_cbm is higher than their final_ counterparts. However, it still shouldn't return any results.

Dino Numić Over a year ago

@oliverbj Updated my answer. Hope this works now. Sorry for the small misunderstanding.

Collectives™ on Stack Overflow

Laravel query scope with multiple where clauses returns incorrect data

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related