1

Using the accepted answer here, I wanted to return all objects based on created_at.

$projects = [ {id => 1,..., 'created_at' => "2017-05-15 14:46:32",...}, ]

$time = strtotime('2017-05-15'); // format issue?

$newformat = date('Y-m-d',$time); // "2017-05-15"

$projects = $user->projects->where('created_at', $newformat);

Im getting back an empty array for $projects, same in tinker. Yes, eloquent is perfect. Am I going it the right way?

Since I am here, I need to query two dates ie $from and $to with whereBetween:

$projects = $user->projects->whereBetween('created_at', [$from, $to])->toArray();

I cant remember where I saw whereBetween from but that give me the error: $from and $to would be similar to $newformat.

Method whereBetween does not exist.

Community
  • 1
  • 1
Sylar
  • 11,422
  • 25
  • 93
  • 166
  • `$projects = $user->projects->whereBetween('created_at', [$from, $to])->get();` Did you try this one? – Quynh Nguyen May 15 '17 at 15:56
  • https://laravel.com/docs/5.2/queries#where-clauses – Quynh Nguyen May 15 '17 at 16:01
  • @QuỳnhNguyễn i, yes, i have tried that but keep getting `Method whereBetween does not exist.` Do i need to add that to my model? Sorry, Im new to laravel. I have a read. thanks – Sylar May 15 '17 at 16:02
  • @QuỳnhNguyễn That won't work; `$user->projects` is a `Collection` not a `QueryBuilder`. `Collection`s don't have a `whereBetween` method. – Tim Lewis May 15 '17 at 16:02
  • @Sylar Yes please try to `$projects = $user->projects()->whereBetween('created_at', [$from, $to])->get();` – Quynh Nguyen May 15 '17 at 16:03
  • @TimLewis Yes sir. My mistake. It's should be `projects()` not `projects` – Quynh Nguyen May 15 '17 at 16:04
  • Ok but my array is empty. If I include the time, I get what I expected. How to query with just the date for `created_at`? – Sylar May 15 '17 at 16:06

2 Answers2

3

You need to specify projects as a method, not a property:

$projects = $user->projects()->where('created_at', $newformat)->get();

Trying to access $user->projects without the () will return a Collection, which doesn't have the whereBetween method. You would have to use:

$user->projects()->whereBetween('created_at', [$from, $to])->get();

In that instance. Note that Collections have a where method, but it doesn't work the same was as Eloquent's.

https://laravel.com/docs/5.4/collections#method-where

If you want to query dates, I find it best to use whereRaw():

$user->project()->whereRaw("DATE_FORMAT(`created_at`, '%Y-%m-%d') BETWEEN '".$from."' AND '".$to."'");

And if only querying a single date:

$user->project()->whereRaw("DATE_FORMAT(`created_at`, '%Y-%m-%d') = '".$newFormat."'");

I should also add that there is a whereDate() clause available as a shorthand to the above:

https://laravel.com/docs/5.4/queries#where-clauses

$user->projects()->whereDate("created_at", $newFormat)

There's also whereYear(), whereMonth() and whereDay(), which could be chained to search for a specific date.

Tim Lewis
  • 27,813
  • 13
  • 73
  • 102
  • Hi. But in tinker `App\Project::where(...)` is still the same – Sylar May 15 '17 at 16:03
  • Might be an issue with your query then. `created_at` has Hours, Minutes and Seconds on it, so `->where("created_at", $newFormat)` would fail. – Tim Lewis May 15 '17 at 16:06
  • Correct. Thats my issue now – Sylar May 15 '17 at 16:07
  • Im now getting `syntax error, unexpected '"'"' (T_CONSTANT_ENCAPSED_STRING)` – Sylar May 15 '17 at 16:13
  • Woops, missed a `.` here `AND '".$to"'"` Should be `AND '".$to."'"` – Tim Lewis May 15 '17 at 16:13
  • What this means: `Undefined function: 7 ERROR: operator does not exist: ` timestamp without time zone` – Sylar May 15 '17 at 16:21
  • Oh you're probably not using `MySQL` are you? Error suggests you're using `postgresql`, which likely doesn't have a `DATE_FORMAT()` method. You'll have to do some debugging there; never used `postgresql` – Tim Lewis May 15 '17 at 16:35
  • No lol sorry. posgresql but it needs to be compatible and on local machines, I use posgresql and other dev,...mysql – Sylar May 15 '17 at 16:37
  • No worries; I made the assumption, so that was my bad. Unfortunately, I have no experience with `postgresql`, so I won't be much help. See if you can google how to query dates in postgres, or see if there's any SO questions on the subject. I'd consider this question closed though; solution to your initial question is there, but created another separate issue. – Tim Lewis May 15 '17 at 16:39
  • No worries. I have tried on another machine and it do works. Thanks. – Sylar May 15 '17 at 16:40
1

You should try:

$newformat = date('Y-m-d',$time).'%'; // "2017-05-15"

$projects = $user->projects()->where('created_at', 'like', $newformat);
ambrooo
  • 134
  • 5