-1

I am trying to get the results of the following query: return the users that are available on a date selected. Users have selected when they are not available and this is stored in a "availabilities" table with a start datetime and an end datetime. Here is what I have been trying without success... I keep on getting the wrong users back and also if they have set more than one unavailability time period it will return them too.

Try #1

User::whereHas('availabilities', function($q)use($selected_date_time)
{
    $q->where('unavailable_start_date', '>', $selected_date_time)
    ->where('unavailable_end_date', '>', $selected_date_time);
})->orWhereHas('availabilities', function($q)use($selected_date_time)
{
    $q->where('unavailable_start_date', '<', $selected_date_time)
    ->where('unavailable_end_date', '<', $selected_date_time);
})->with('availabilities')->get();

Try #2

User::whereHas('availabilities', function($q)use($selected_date_time)->whereHas('availabilities', function($q)use($selected_date_time)
{
    $q->whereRaw('? NOT BETWEEN `unavailable_start_date` AND `unavailable_end_date`', [$selected_date_time]);
})->get();

Here is the mysql query executed in the try #1 case: 

 select * from `users` where `is_user` = ? and (select count(*) from `availabilities` where `availabilities`.`user_id` = `users`.`id` and `unavailable_start_date` > ? and `unavailable_end_date` > ?) >= 1 or (select count(*) from `availabilities` where `availabilities`.`user_id` = `users`.`id` and `unavailable_start_date` < ? and `unavailable_end_date` < ?) >= 1

And the dates vardumped:

selected string(19) "2014-11-13 11:00:00"

unavailableStart string(19) "2014-11-12 11:30:00"

unavailableEnd string(19) "2014-11-18 11:00:00"

Ant idea what's going wrong or how to approach that problem?

EDIT FULL QUERY 

$photographers_available =  Photographer::where('is_photographer', '=', '1')
                            ->whereHas('studioAddress', function($q)use($city_id)
                                                                   {
                                                                        $q->where('city_id', '=', $city_id);
                                                                   })
                            ->orWhereHas('user', function($q)use($city_id)  // TK could reduce to 'user.address' I think
                                                                     { 
                                                                        $q->whereHas('address', function($q)use($city_id) 
                                                                                     {
                                                                                        $q->where('city_id', '=', $city_id);
                                                                                     });
                                                                     })
                           ->whereHas('stypesPhotographer', function($q)use($stype)
                                                                   {
                                                                        $q->where('shooting_type_id', '=', $stype);
                                                                   })
                            ->where(function ($q) use ($selected_date_time) {
                            $q->whereHas('availabilities', function($q)use($selected_date_time)
                            {
                                $q->where('unavailable_start_date', '>', $selected_date_time)
                                ->where('unavailable_end_date', '>', $selected_date_time);
                            })->orWhereHas('availabilities', function($q)use($selected_date_time)
                            {
                                $q->where('unavailable_start_date', '<', $selected_date_time)
                                ->where('unavailable_end_date', '<', $selected_date_time);
                            });
                            })
                            ->with('availabilities')
                            ->get();

After thinking, could the orWhereHas be the one making it go wrong?

Thanks!

commandantp
  • 947
  • 1
  • 12
  • 25

3 Answers3

2
User::whereHas('availabilities', function ($q) use ($dt) {
    $q->where('unavailable_start_date', '<=', $dt)
      ->where('unavailable_end_date', '>', $dt);
}, '=', 0)->get();

So we are kinda reversing this in a way. We are setting up the query inside whereHas to find the availabilities, unavailabilities, that are in this range. So we are querying for all unavailable records (in a way). We are then only grabbing users that dont have relationships that match this criteria, by using the other arguments to whereHas ( '=', 0 ).

So any users that have this relation that fall into this time frame are not returned, no matter how many availability records they have. If any of them qualify they wont make it through the filter.

lagbox
  • 48,571
  • 8
  • 72
  • 83
  • that's crazy, the second user always shows up! Not the first one thought! Here is when I test manually the user that is showing up I do get false for the logic part just by testing dates > or < . Always the same showing up :O – commandantp Nov 12 '14 at 07:56
  • what format is the unavailable_start_date column and what format is $dt that you are using? – lagbox Nov 12 '14 at 08:08
  • both same format: string(19) "2014-11-13 11:00:00" when compared to each other I get the good answer -> see my question edited – commandantp Nov 12 '14 at 10:01
  • For whoever reading please see @jarek 's comments below. Need to nest the orWhere and then it runs perfect with this solution! – commandantp Nov 12 '14 at 12:54
  • do i need to update this answer, following this has become a little confusing :). – lagbox Nov 12 '14 at 23:06
1

EDIT tldr; Remember to use nested where for orWhatever mehtods. Other than that, @lagbox gave you correct answer to your originally posted issue. Here's full solution:

$photographers_available =  Photographer::where('is_photographer', '=', '1')
->where(function ($q) use($city_id){
    $q->whereHas('studioAddress', function($q)use($city_id) {
        $q->where('city_id', '=', $city_id);
    })
    ->orWhereHas('user', function($q)use($city_id) {
        $q->whereHas('address', function($q)use($city_id) {
            $q->where('city_id', '=', $city_id);
        });
    });
})
->whereHas('stypesPhotographer', function($q)use($stype) {
    $q->where('shooting_type_id', '=', $stype);
})
->where(function ($q) use ($selected_date_time) {
$q->whereHas('availabilities', function($q)use($selected_date_time) {
    $q->where('unavailable_start_date', '>', $selected_date_time)
    ->where('unavailable_end_date', '>', $selected_date_time);
})->orWhereHas('availabilities', function($q)use($selected_date_time) {
    $q->where('unavailable_start_date', '<', $selected_date_time)
    ->where('unavailable_end_date', '<', $selected_date_time);
});
})
->with('availabilities')
->get();

Assuming your where clauses are OK for your case, this is what you need:

User::where(function ($q) use ($selected_date_time) {
    $q->whereHas('availabilities', function($q)use($selected_date_time)
    {
        $q->where('unavailable_start_date', '>', $selected_date_time)
        ->where('unavailable_end_date', '>', $selected_date_time);
    })->orWhereHas('availabilities', function($q)use($selected_date_time)
    {
        $q->where('unavailable_start_date', '<', $selected_date_time)
        ->where('unavailable_end_date', '<', $selected_date_time);
    });
})->with('availabilities')->get();

because, as we can see here:

select * from `users` 
  where `is_user` = ? 
  and (...) >= 1 
  // culprit:
  or (...) >= 1

you need nested orWhere due to (probably) a global scope.

Jarek Tkaczyk
  • 78,987
  • 25
  • 159
  • 157
  • Damn it is returning the wrong user... this is not easy! your first where clause has no condition? – commandantp Nov 12 '14 at 12:22
  • I haven't gone into details of your whole story, so I don't know what that means, but this part was obviously wrong (`x AND y OR z` instead `x AND (y OR z)`). Yes, `where` has no condition, since it's there for nesting those other `wheres`. So, to sum up your issue - you have `unavailabilities` table (forget the name for now, I mean its purpose) and you want to fetch users that are available for given date, correct? – Jarek Tkaczyk Nov 12 '14 at 12:27
  • Correct, my table availabilities has a foreign key for the user and has the start and finish date of the unavailability of the user. – commandantp Nov 12 '14 at 12:29
  • Ok just realized the previous query might be conflicting. Bear with me one minute I will drop the whole query in the question. @jarek full query now in the question, as you can see I was doing 3 Where before that one for different reasons but so far it was returning the correct things – commandantp Nov 12 '14 at 12:31
  • Well, if that's the case, then @lagbox already gave you correct solution. Unless you mean something else in the end.. – Jarek Tkaczyk Nov 12 '14 at 12:35
  • Ahh, now you should already know the answer! Seeing your edited (whole) query, it's obvious that you have to nest your `whereHas` and `orWhereHas` clauses like I showed you. – Jarek Tkaczyk Nov 12 '14 at 12:37
  • crazy! I was blind! Ok let me try. – commandantp Nov 12 '14 at 12:37
  • You've got it all in my answer now. – Jarek Tkaczyk Nov 12 '14 at 12:41
  • Awesome thanks! I will remember to nest when using orW... for the next time! I validated @lagbox's answer because indeed it is the solution to retrieve my results. Thank you soooo much! I can finally move on :D – commandantp Nov 12 '14 at 12:52
  • not sure if you are still around but how would you access datas nested here? Say I want the minimum price which is nested under "stypesPhotographer" - I haven't been able to find how so far and I will need to access that and also reorganize to display results in different orders... – commandantp Nov 14 '14 at 10:23
  • Not sure what you mean. Write example code describing which field you need, ie. `modelX->relatedY->price`. – Jarek Tkaczyk Nov 14 '14 at 11:55
  • Say I want to access this $photographers_available->stypesPhotographer->price. In order to get the minimum price for example. I will also need to access it to re-order the collection by increasing price for example – commandantp Nov 14 '14 at 14:43
  • Well, `photographers` is a collection, so you need its single element to access its related `stype`. Apart from that just use eager loading `with('stypesPhotographer')` on the query. – Jarek Tkaczyk Nov 14 '14 at 20:33
  • But does that mean I cannot load the collection with those attributes sortable? How would sort the results by price if the price was nested like that? My backup plan will be to use arrays but it will mess up the view as I am accessing stuff from the collection in the view – commandantp Nov 15 '14 at 08:10
  • I don't get what you mean by *reorder by increasing price*. You can sort the collection by whatever property of its items - check the docs for info. – Jarek Tkaczyk Nov 15 '14 at 13:23
  • Yes that's what I meant order by. But the docs don't say how to re order by a criteria nested like in my case the price...any idea? – commandantp Nov 16 '14 at 16:33
  • The docs say exactly how. You pass a closure and return whatever value you want to order by, be it item property, item method return value or anything nested however you like. – Jarek Tkaczyk Nov 16 '14 at 21:48
  • I will open another question because I can't seem to make it work like I want to.... :-( – commandantp Nov 17 '14 at 11:45
0

try this works well for me...

if using Eloquent ORM use:

 User::Where(function($query)  use ($selected_date_time)
{
        $query->where('unavailable_start_date','>',$selected_date_time);    
        $query->where('unavailable_end_date','>',$selected_date_time);  
})
->orWhere(function($query)
{
        $query->where('unavailable_start_date','<',$selected_date_time);    
        $query->where('unavailable_end_date','<',$selected_date_time);  
})

The above would be something like this (in my table it results in below query)

select * from `equipments` where (`created_at` > '2014-10-29' and `updated_at` > '2014-10-29') or (`created_at` < '2014-10-29' and `updated_at` < '2014-10-29')

comment if not working...

Ronser
  • 1,855
  • 6
  • 20
  • 38