Laravel ORM is giving an incorrect result on a simple query

Question

I really don't get it, help me understand.

I'm writing an app which among other things calculates a NFL team "mark" (a mark is Win/Lose/Tie, sorry I don't know the word in English for that), so I have a "Marca" attribute in the Team model, looks like this:

public function getMarcaAttribute() {
    ...
}

Queries are very easy, first I get the number of games where the team is playing as local and calculate wins, loses and ties, for example, this one is for wins:

$gan += Juego::where('local', $this->id)
                  ->where('score_local', '>', 'score_visitante')
                  ->get()->count();

Then I do the same with games as visitor, but inverting comparison signs, of course.

Now, lets take a look at game Seattle (38) at Atlanta (25), if I do in the database

SELECT COUNT(*) FROM juegos WHERE local='atl' AND score_local > score_visitante;

Of course, it returns 0.

In the ORM, the generated query is:

  array (
    'query' => 'select * from `juegos` where `local` = ? and `score_local` > ? and `score_local` is not null',
    'bindings' => 
    array (
      0 => 'atl',
      1 => 'score_visitante',
    ),
    'time' => 0.89,
  ),

The thing is returning a 1. I even replaced the ->count()->get() for a ->get() and do a foreach over the results:

$gan = Juego::where('local', $this->id)
              ->where('score_local', '>', 'score_visitante')
              ->get();

Log::info('Ganados');
foreach ($gan as $g) {
  Log::info("$g->score_local > $g->score_visitante");
}

The thing is returning a line where it says "25 > 38"

I really don't understand what happens here. Any idea?

because `where` is taking a "value" and comparing it to the column not comparing 2 columns ... perhaps you want `whereColumn` — lagbox, Sep 15 '20 at 17:08
Does this answer your question? [Laravel Eloquent Compare Column Values](https://stackoverflow.com/questions/30331437/laravel-eloquent-compare-column-values) — iainn, Sep 15 '20 at 17:09
What???? so it is comparing against 'score_visitante' as a string and not as a column???? This is frustrating. I thought I was following documentation: https://laravel.com/docs/5.0/queries#advanced-wheres — luisfer, Sep 15 '20 at 17:19
so then why would you be using the documentation for Laravel 5.0 ? you are just frustrating yourself — lagbox, Sep 15 '20 at 17:21
Because I went to Google... tell me where is that in 7. It hasn't changed that much in the ORM. I was doing this from my memory... `where(column, comparisor, compare_to_what)`. Thank you a lot anyway, I just learned not only how (whereRaw) but also why (your answer) — luisfer, Sep 15 '20 at 17:22
go to the version 7.x of the docs (the upper right is the version drop down) and then type `whereColumn` into the search bar and you will get to the section and then you can scroll through it and you will find it — lagbox, Sep 15 '20 at 17:24
Now that I review what you say and what I got in the logs makes perfectly sense, in the bindings it is goingo for a value, not a column. Thanks a lot. — luisfer, Sep 15 '20 at 17:28
https://scotch.io/tutorials/debugging-queries-in-laravel looks helpful — ysth, Sep 15 '20 at 17:40
Thanks! there's a chrome extension (I don't recall the name, I have it in my office PC) that allows you to do some debugging in the laravel, including the "calls" to the database — luisfer, Sep 15 '20 at 17:44

score 3 · Accepted Answer · answered Sep 15 '20 at 17:11

3

You can achieve this using whereRaw :

$gan = Juego::where('local', $this->id)
              ->whereRaw('score_local > score_visitante')
              ->get();

Or as suggested in comments, whereColumn:

$gan = Juego::where('local', $this->id)
              ->whereColumn('score_local', '>' , 'score_visitante')
              ->get();

answered Sep 15 '20 at 17:11

Pirvu2k

151
1
7

Yes it does, `->whereRaw()` works as I wanted. I don't know why the heck is `->where()` for, `->whereRaw()` is more compact and more readable. Thanks a lot. I've been cursing all morning long!!! – luisfer Sep 15 '20 at 17:20
3

@luisfer In most cases when you're using `where()`, you're supplying a value to check the database against, not specifying another column... The syntax is roughly equivalent `where($column, $operator, $value)`, not `where($column, $operator, $anotherColumn)`. That's why the `whereRaw()` or `whereColumn()` methods exist. Don't rely on `whereRaw()` too much, as Laravel is supposed to be language-agnostic, meaning that it will work for all DB languages (MySQL, Postgres, etc), but `whereRaw()` doesn't work that way. That all being said, this answer handles your case perfectly :) – Tim Lewis Sep 15 '20 at 17:22
2

Tim is right. I would add that if at any point you want to use dynamic values in `whereRaw` queries be very careful to use the correct bindings to avoid sql injections: https://laravel.com/docs/8.x/queries#raw-methods – Pirvu2k Sep 15 '20 at 17:26
1

Right... I knew that `whereRaw()` existed but I wanted to be, as you say, agnostic. I mostly work with mySQL for my own things, at work we use much more Postgres. In this case, I'll keep the `whereRaw()` since its for me... thanks! – luisfer Sep 15 '20 at 17:27

emiliopedrollo · Answer 2 · 2020-09-15T17:45:39.943

1

@lagbox had it figured out in the comments. The problem is that by using prepared statements with bindings the term score_visitante is not been treated as a column name rather than a string.

There are several ways to solve it:

Use Eloquent method whereColumn:

$gan += Juego::where('local', $this->id)
                  ->whereColumn('score_local', '>', 'score_visitante')
                  ->count();

Use Eloquent method whereRaw:

$gan += Juego::where('local', $this->id)
                  ->whereRaw('score_local > score_visitante')
                  ->count();

Use DB::raw:

$gan += Juego::where('local', $this->id)
                  ->where('score_local', '>', \DB::raw('score_visitante'))
                  ->count();

Note: You also do not need the ->get() before the ->count(). You can instruct the database to do the counting. This is particularly useful when the expected result is a large dataset with many rows, by returning just a number instead of potentially thousands or even millions of rows you are preserving your network resources.

edited Sep 15 '20 at 17:45

answered Sep 15 '20 at 17:29

emiliopedrollo

910
6
21

Yeah... I'll have to check a lot of queries where I have tried to compare columns... maybe they were working by mere luck. – luisfer Sep 15 '20 at 17:31
BTW, the `->whereNotNull()` is unnecesary, it was part of my frustration, I thought it may be counting NULL values (which of course would make no sense), I'll edit the question – luisfer Sep 15 '20 at 17:32
OK thanks, that's new... so I end the sentence with just `->count()`? Thanks a lot! – luisfer Sep 15 '20 at 17:45
1

@luisfer Calling `->get()` on a Query (a `Builder` instance) returns a Collection, which has a `count()` method. Conveniently, the `Builder` class also has a `count()` method, so you can call `->count()` directly and bypass the need for a Collection. – Tim Lewis Sep 15 '20 at 18:38

Laravel ORM is giving an incorrect result on a simple query

2 Answers2