How to search between two dates in laravel vue-CodePudding

i have a form that has search for some data

i am able to make a search for normal fields

but i am having trouble getting date range

controller:

public function index() {

$query = Matter::query();

$query->when(request('search_client','search_file_ref','search_file_status','search_date_from','search_date_to'), function($query){
$query->where('client_company', 'like', '%' . request('search_client') . '%');
$query->where('file_ref', 'like', '%' . request('search_file_ref') . '%');
$query->where('status', 'like', '%' . request('search_file_status') . '%');

$query->whereBetween('created_at', ['search_date_from', 'search_date_to']);
});
return $query->orderBy('id','asc')->get();

}

View:

                <b-col md="4">
                    <form @submit.prevent="searchDateFrom">
                        <label for="">Choose File Status:</label>
                <div >

                    <input
                        v-model="search_date_from"
                        type="date"
                        placeholder="Search File Status"
                        
                    />
                    <input
                        v-model="search_date_to"
                        type="date"
                        placeholder="Search File Status"
                        
                    />
                    <div >
                        <button type="submit" >
                            <i ></i>
                        </button>
                    </div>
                </div>
            </form>
            </b-col>

...

data(){
    return {
        search_client: "",
        search_file_ref: "",
        search_file_status: "",
        search_date_from: "",
        search_date_to: "",
        matters:[]
    }
},

...

searchDateFrom(){
        axios.get('/api/auth/matter?search_date_from='   this.search_date_from   'search_date_to='   this.search_date_to)
        .then(response => this.matters = response.data)

    },

so when i try to do this in controller:

$query->whereBetween('created_at', ['2022-06-01', '2022-06-07']);

i'm getting the data and correct range

but when i add the request to it like this:

$query->whereBetween('created_at', ['search_date_from', 'search_date_to']);

i stop getting data from API and nothing shows

Model:

{
use HasFactory;
protected $fillable = [
    'matter_type','client_company','description','file_group',
    'control_account','pic','lawyer','task_assign','task_recipient',
    'file_ref','remark','purchaser_1','purchaser_2','status'
];
protected $casts = [
'created_at' => 'datetime:m / d / Y',
];
}

am i doing anything wrong with the request?

i'm able to get data and search other fields (other than date)

and trying to achieve search field so the user is able to set two dates and get the results between this range

CodePudding user response：

Your query is correct but you made a small mistake as you forget to wrap your date params in the request helper. Instead of passing dates from request, you were passing the strings literals search_date_from and search_date_to to the whereBetween statement.

Your code

$query->whereBetween('created_at', ['search_date_from', 'search_date_to']);

Corrected code

$query->whereBetween('created_at', [request('search_date_from'), request('search_date_to')]);

Kindly see the bellow corrected code.

public function index() {

        $query = Matter::query();

        $query->when(request('search_client','search_file_ref','search_file_status','search_date_from','search_date_to'), function($query){
            $query->where('client_company', 'like', '%' . request('search_client') . '%');
            $query->where('file_ref', 'like', '%' . request('search_file_ref') . '%');
            $query->where('status', 'like', '%' . request('search_file_status') . '%');

            $query->whereBetween('created_at', [request('search_date_from'), request('search_date_to')]);
        });
        return $query->orderBy('id','asc')->get();

    }

CodePudding user response：

First of all you have written your variables as string here. $query->whereBetween('created_at', ['search_date_from', 'search_date_to']);

You can write like this $query->whereBetween('created_at', [$search_date_from, $search_date_to]);

I prefer writing like this one: $query->whereBetween(DB::raw("DATE('created_at')"), array($from, $to));