Date Query

I have a search box on my admin dashboard, currently it searches by first name, last name, or type. I would like to add search by created_at, published_at and so on with date fields that i have.

? Obit::where('first_name', 'LIKE', "%$query%")
                ->orWhere('last_name', 'LIKE', "%$query%")
                ->orWhere('type', 'LIKE', "%$query")
                ->paginate(15)
            :   $obits = Obit::paginate(15);

i tried

orWhere('published_at', 'LIKE', "%query")

returned no results, I am not sure if the search has to be formatted a specific way if searching for a date, or if i'm going about it all wrong...

Croisciento

10 years ago

Level 1

You can use mutators. Here's a link to the doc : http://laravel.com/docs/5.1/eloquent-mutators#date-mutators

For example if you want to define a scope where you retrieve all the records that are published you could so something like this :

public function scopePublished($query)
    {
        $query->where('published_at', '<=', Carbon::now());
    }

You have to remember that if you want to use date mutators you have to tell laravel which attributes from your database can be considered as such.

protected $dates = ['published_at'];

Then you can take a look at the official documentation of Carbon which will give you answers on how to mutate your published_at attribute the way you want.

jbowman99

10 years ago

Level 7

@Croisciento

I would like to return a specific day, not sure if Laravel requires a specific formatted date to search for. if someone entered 9/24/2015 into the search field i would like it to return any records with that published_at date.

thanks for your suggestion

Croisciento

10 years ago

Level 1

The purpose of Carbon is to give you an easy way to manipulate php date objects. What I personaly ran into was the same issue you're facing right now.

When you make a query on an date or datetime column on your database you have to make sure that the attribute to compare to (in your case the input given by the user) follows the same format as the one on your database. Like won't change a thing here.

That's why Carbon is really useful.

As an addition to my previous answer you can tell Laravel to format the date in a certain format. For example :

{!! Form::input('date', 'published_at', $model->published_at->format('Y-m-d)' !!}

you can use the format() method because Laravel knows its a carbon instance

Then you can also tell Laravel that each time you're manipulating the published_at attribute you want him to convert this value to a Carbon instance :

public function getPublishedAtAttribute($date) {
    return new Carbon($date);
}

This way each time an user is going to send you a form, the published_at attribute is going to be converted to a Carbon instance, but also when you're trying to make a simple query like yours :

orWhere('published_at', 'LIKE', "%query")

If you're unsure of this method i'd invite you to dd() the value sent selected by your user and the one you're actually trying to compare to and see it's not the same and as such it's a normal behaviour to return no results.

Comparing dates or datetimes columns can be a mess, and Carbon makes your life easier.

Please or to participate in this conversation.