CODEWITHSUNDEEP
phpmysqllaraveleloquent
Chris Townsend
Chris Townsend

Reputation: 2716

Laravel where 'less than' query not showing expected results

I am trying to make a database query where I search for all of the items which have a lower current level than low stock level. When running the query I am getting no results and I'm not sure why.

This is my query

public static function getLowStockItemsCache()
{
         dd(\DB::table('items')->where('current_level', '<', 'low_stock_level')->get());
    }

When I die and dump this I get an empty collection.

In my database I have the following records

enter image description here

Both of these fields at set at int(11)

If I reverse the query I get all 13 records.

Am I missing something small, as it's confusing me greatly and should be simple.

Upvotes: 8

Views: 26941

Answers (2)

Codedreamer
Codedreamer

Reputation: 1702

Laravel use 3 step argument on the "where" clause, 

Pair::where('1st','2nd','3rd')

by default the 2nd arg is normally default equal sign, saying the 1st arg is equal to the 3rd arg,

Pair::where('name', 'john')->get();

Pair::where('stock', '<', '100')->get();

When you find this logics then you understand the "where" tricks

Upvotes: -2

Bogdan
Bogdan

Reputation: 44526

The third parameter is the value parameter which means it will be escaped when building the query to avoid SQL injection. If you want to compare two columns, you have to specifically tell the Query Builder that that part of the query should not be processed, either by using whereRaw to pass the raw SQL condition:

\DB::table('items')->whereRaw('current_level < low_stock_level')->get());

Or by using DB::raw() for the value only, so it's not escaped:

\DB::table('items')->where('current_level', '<', DB::raw('low_stock_level'))->get());

Upvotes: 24

Related Questions