Level 10
From Example, This is Trips Table ( https://prnt.sc/xsv38zYUBdPU ) If I Query For Guides with a certain period (2022-06-05 - 2022-06-10) i should not get this guide because he's not available in this period and had already trip
Jun 6, 2022
11
Level 10
How To Get User Dosn't Had Any Trips !
Hello I Have Guide Table And Trips Table
Relationship Guide HasMany Trips & Trip BelongTo Guide
Every Trip Have from_date And to_date
My question is ... How do I get all the guides available for booking in a certain period ( From-To )
Level 10
I Tried This but it does not work as I expected
->WhereHas('trips',function (Builder $query) use ($request){
$from = Carbon::parse($request->get('from_date'))->startOfDay()->toDateTimeString();
$to = Carbon::parse($request->get('to_date'))->endOfDay()->toDateTimeString();
$query
->whereNotBetween('from_date', [$from,$to])
->whereNotBetween('to_date', [$from,$to]);
})
$from & $to Came From Request
Level 70
@msaied Can you show your query how you wrote that?
1 like
Level 10
@tisuchi Thanks For Your Reply ♥
$users = User::with(['guideServices','trips'])
->WhereHas('trips',function (Builder $query) use ($request){
$from = Carbon::parse($request->get('from_date'))->startOfDay()->toDateTimeString();
$to = Carbon::parse($request->get('to_date'))->endOfDay()->toDateTimeString();
$query
->whereNotBetween('from_date', [$from,$to])
->whereNotBetween('to_date', [$from,$to]);
})
->active()
->availableForEmployment()
->get();
Level 20
Use whereHas() method
Guide::whereHas('trips', function ($query) {
$query->whereDate('from_date ', '>=', today()->subMonth())->whereDate('to_date', '<=', today());
})->get();
Level 10
@petrit Hello, the trip has the date as well as Start And End
Level 10
@petrit I already thought on this but it's not working ( https://prnt.sc/eroP5uW1dUyT )
I searched From ( 2022-06-5 - 2022-06-10 ) and it showed me the guide which has already trip in ( 2022-06-07 - 2022-06-10 ) So This User Should not be shown except if I search from ( 2022-06-5 - 2022-06-6 ) so This guide did not have any trip in this period
Level 20
I think your date format is not recognized by carbon, so use
$from = Carbon::createFromFormat('Y-m-d', $request->from_date);
$to= Carbon::createFromFormat('Y-m-d', $request->to_date);
//...
Level 10
@petrit it's recognized already and works as expected ( https://prnt.sc/YnPhuBQEQek5 )
Level 10
I found a solution, But it works in the opposite direction
$query
->whereBetween('from_date',[$from,$to])
->orWhereBetween('to_date',[$from,$to]);
with this query, if I search with a date already has trip it show user if i search in date where no trip in this date it re turn empty which is work right but i need to reverse it i use WhereNotBetween but it's not work
Level 10
I figured out the solution from this reply and it works like charm ♥
->whereNotIn('id',function ($query) use ($request){
$from = Carbon::parse($request->get('from_date'))->toDateString();
$to = Carbon::parse($request->get('to_date'))->toDateString();
$query->from('trips')
->select('guide_id')
->where('from_date', '<=', $to)
->where('to_date', '>', $from);
})
1 like
Please or to participate in this conversation.