Tuesday, February 28, 2017

Laravel query different result between (identical?) two vs three argument 'where' clause

I'm having a very odd issue with a query in laravel (5.2) - I've got a collection created from some external source (an API), and I'm trying to run a 'where' query to extract specific records.

Originally, I was trying to extract all entries which were submitted during the current month (so, after the first day of this month)

$entries is the starting collection (time entries on a project - see end of post)

$thisMonthStart = (new Carbon('first day of this month'))->toDateString();
  //value of this is 2017-02-01, and the issue is not resolved if I remove toDateString()

$entriesThisMonth = $entries->where('spent-at', '>', $thisMonthStart); 
  //returns an empty collection, but should have 15 results

Now the really odd part, is that I tried instead to get $entries where 'spent-at' is equal to the first day of the month - there should be one entry. If I don't explicitly specify the comparison operator, I get my expected result:

$entriesThisMonth = $entries->where('spent-at', $thisMonthStart);
  //one $entries returned, see end of post

However if I specify the = operator

$entriesThisMonth = $entries->where('spent-at', '=', $thisMonthStart);
  //empty collection returned

So I'm now very confused - presumably something is wrong in my original collection, but why does the specifying vs not specifying the operator make any difference? I would have thought that those two queries would give identical results?

(and obviously, not being able to specify the operator is not very helpful when trying to do a < or > comparison, but I'm mostly just interested in what the actual difference is between those two syntaxes, and so why they give different results?)

I couldn't find any info anywhere on how these two versions of the query work and so if it's expected that they could give different results - I would think that they should be identical, but maybe someone with a deeper understanding could explain what's causing this?

Thank you to anyone who can shed some light on the mystery!


A sample of the $entries collection in case is of any use (just a single record): (NB there are definitely records from the current month, I know this example is too old)

Collection {#952 ▼
  #items: array:367 [▼
175412141 => DayEntry {#958 ▼
  #_root: "request"
  #_convert: true
  #_values: array:16 [ …16]
  +"id": "175412141"
  +"notes": ""
  +"spent-at": "2013-10-03"
  +"hours": "0.75"
  +"user-id": "595841"
  +"project-id": "4287629"
  +"task-id": "2448666"
  +"created-at": "2013-10-03T18:07:54Z"
  +"updated-at": "2013-11-01T12:50:51Z"
  +"adjustment-record": "false"
  +"timer-started-at": ""
  +"is-closed": "false"
  +"is-billed": "true"
  +"started-at": "10:45"
  +"ended-at": "11:30"
  +"invoice-id": "3633772"
}

And this is what is returned by the where query without the operator:

Collection {#954 ▼
  #items: array:1 [▼
568944822 => DayEntry {#1310 ▼
  #_root: "request"
  #_convert: true
  #_values: array:15 [▶]
  +"id": "568944822"
  +"notes": "Tweaking formatting on job ads and re shuffling ad order"
  +"spent-at": "2017-02-01"
  +"hours": "0.25"
  +"user-id": "595841"
  +"project-id": "4287629"
  +"task-id": "2448666"
  +"created-at": "2017-02-01T14:45:00Z"
  +"updated-at": "2017-02-01T14:45:00Z"
  +"adjustment-record": "false"
  +"timer-started-at": ""
  +"is-closed": "false"
  +"is-billed": "false"
  +"started-at": "14:30"
  +"ended-at": "14:45"
}
]
}




via Roxy Walsh

Advertisement