CODEWITHSUNDEEP
mysqldatabaselaravel
ilham zacky
ilham zacky

Reputation: 51

Database Filter Issue On Laravel

I am Using Laravel

below mentioned is one of my db table columns :

{ "body": { "age": { "dob": "1960-01-28", "age_in_years": 60, "dob_computed": false }, "gender": "female", "address": { "street": "No.341,KC De silvapura", "country": "LK", "locality": "Thimbirigaskatuwa", "postcode": "N/A" }, "comment": "", "id_type": "NID", "id_number": 869000000000, "last_name": "Dayawansa", "member_id": "", "first_name": "jj", "preferred_name": "", "contact_details": { "email": "", "phone_number_alternate": "", "phone_number_preferred": 716900761 }, "consent_obtained": true, "alternate_contacts_details": { "name": "", "email": "", "phone_alternate": "", "phone_preferred": "" } }, "meta": { "end_time": "2019-09-06", "start_time": "2019-09-06", "registered_by": "24f57630-a102-11ea-a415-23a06eeb8904" } }

my code is My controller :

 $start_date = $request->has('startDate') ? $request->get('startDate') : Carbon::now()->subDays(30)->format('m/d/Y');
 $end_date = $request->has('endDate') ? Carbon::parse($request->get('endDate'))->addDays(1)->format('m/d/Y') : Carbon::now()->addDays(1)->format('m/d/Y');

if i use created at it works

$participants = Participant::Where('created_at', '>=', $start_date)
        ->where('created_at', '<=', $end_date);

if is use start_time which is inside a array it returns empty

$participants = Participant::Where('data->meta->start_time', '>=', $start_date)
        ->where('data->meta->start_time', '<=', $end_date);

this isn't working any suggestion where am i wrong ?

Upvotes: 0

Views: 68

Answers (2)

Mukti Rani Ghosh
Mukti Rani Ghosh

Reputation: 577

I have tested your code and I think you should change your date format and you should receive startDate and endDate in the same format form request as your column value has. Like, you have the start_time something like that "start_time": "2019-09-06" in your DB. So, use the same date format when you will compare it.

Test your code in this way.

$start_date =  Carbon::parse('2019-09-06')->format('Y-m-d');
$end_date = Carbon::parse('2019-09-10')->format('Y-m-d');

You will get a response but if you use the below format then you will not receive any response.

$start_date =  Carbon::parse('2019-09-06')->format('m/d/Y');
$end_date = Carbon::parse('2019-09-10')->format('m/d/Y');

Your query is fine. Just make sure that your json column name is data.

$participants = Participant::where('data->meta->start_time', '>=', $start_date)
                ->where('data->meta->start_time', '<=', $end_date)->get();

Upvotes: 1

Joel Corona
Joel Corona

Reputation: 61

Use whereBetween method of elocuent:

$start_date = $request->has('startDate') ? $request->get('startDate') : Carbon::now()->subDays(30)->format('m/d/Y');
$end_date = $request->has('endDate') ? Carbon::parse($request->get('endDate'))->addDays(1)->format('m/d/Y') : Carbon::now()->addDays(1)->format('m/d/Y');

$participants = Participant::whereBetween('start_time', [$start_date , $end_date])->get(); // or ->first() ur choice.

to more details

Upvotes: 0

Related Questions