Jan Ariel San Jose Jan Ariel San Jose - 1 month ago 13
MySQL Question

Laravel 5.4 Query Builder - Where condition with 2 table fields

So I'm trying to use a query on selecting

products
that are on Critical Level. So basically, if the product's
quantity
is lower than its
reorder_point
, it'll be considered as Critical.

Here's my
query
that I'm using:

$products = DB::table('inventory')
->where('quantity', '<=', 'reorder_point')
->orderBy('quantity', 'asc')
->get();


But it only shows once the
quantity
of that row is set to
0
or less. So I'm asumming that the value of
re_orderpoint
in the where condition is
0
.

But everything works when I use this query in
phpMyAdmin
:

SELECT * from inventory where quantity <= reorder_point

Answer Source

Laravel gives you whereColumn for comparing columns of same table. You can do it like this:

$products = DB::table('inventory')
        ->whereColumn('quantity', '<=', 'reorder_point')
        ->orderBy('quantity', 'asc')
        ->get();

See docs here. Hope you understand.