Laravel QueryBuilder - Different result with `where()` and `whereRaw()` for the identical Query

user_2867881 0
Viewed 935

I'm working on a laravel application Where I have two very similar QueryBuilder but producing different result in both conditions.

Query 1:

$ids = $this->model->leftJoin('feed', 'agents.identifier', '=', 'feed.identifier')
            ->whereRaw('feed.active <> agents.feed_status')
            ->pluck('id');

dd(count($ids)); // print 485236

Query 2:

$ids = $this->model->leftJoin('feed', 'agents.identifier', '=', 'feed.identifier')
            ->where('feed.active', '<>', 'agents.feed_status')
            ->pluck('id');

dd(count($ids)); // print 4259

I would like to know the key difference between these two QueryBuilder. Why is it producing different results, although it seems identical?

And which query returns the correct result? if I would like to find the records from agents where feed_status is not equel to feed.active.

laravel-query-builder laravel mysql
2 Answers

it seem I have got the clarification. Howevere I would like to share my R&D here. Incase if anyone else got the same problem.

I have printed the raw query and get where() seems consider the third parameter as string compare instead of field compare. That's why seems the result is different.

However when we run the query with whereRaw() it's treated this as table field comparision.

Laravel Code:

$ids = $this->model->leftJoin('feed', 'agents.identifier', '=', 'feed.identifier')
            ->whereRaw('feed.active <> agents.feed_status')
            ->pluck('id');

MySql Query:

"select * from `agents` left join `feed` on `agents`.`identifier` = `feed`.`identifier` where feed.active <> agents.feed_status"

# where feed.active <> agents.feed_status

Laravel Code: 

$ids = $this->model->leftJoin('feed', 'agents.identifier', '=', 'feed.identifier')
            ->where('feed.active', '<>', 'agents.feed_status')
            ->pluck('id');

MySql Query: 

"select * from `agents` left join `feed` on `agents`.`identifier` = `feed`.`identifier` where `feed`.`active` <> 'agents.feed_status'"

# where feed.active <> 'agents.feed_status'

user_28678810

Yes, the results were meant to be different.

As where method compares a column with a literal value

->where('table.column', 'cond', 'value')

If you are looking to make comparisons in two columns without using whereRaw method; you should instead use whereColumn method

->whereColumn('table1.column1', 'cond', 'table2.column2')
user_110702640
Related