如果所选日期介于两个日期之间，如何从查询中获取结果？ [英] How to get results from the query if the selected date is between two dates?

问题描述

我想获得以下查询的结果：返回在所选日期可用的用户。用户选择何时不可用，并将其存储在具有开始日期时间和结束日期时间的可用性表中。
这是我一直在尝试没有成功...我继续得到错误的用户回来，如果他们设置了一个以上的不可用时间段，它会返回它们。

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.

尝试＃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();

尝试＃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();

这是在try＃1情况下执行的mysql查询：

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

日期vardumped：

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想法出现了什么问题或如何解决这个问题？

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

EDIT 全部查询

$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();

经过思考，orWhereHas是否会导致错误？

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

谢谢！

推荐答案

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

所以我们可以用某种方式逆转这个。我们在其中设置查询，以找到在此范围内的可用性，不可用性。因此，我们正在查询所有不可用的记录（在某种程度上）。然后，我们通过使用whereHas（'='，0 ）的其他参数来抓取没有符合此条件的关系的用户。

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.

这篇关于如果所选日期介于两个日期之间，如何从查询中获取结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！