代码之家 › 专栏 › 技术社区 › Ber Tsacianegu del Tepuy

雄辩:使用不存在的列进行查询

filtering eloquent orm laravel

Ber Tsacianegu del Tepuy · 技术社区 · 7 年前

我正在尝试以下操作:我有两个模型(pub和schedule)与一个1xn关系相关,如下所示:

Pub:

/**
 * @return \Illuminate\Database\Eloquent\Relations\HasMany
 */
public function pubSchedules()
{
    return $this->hasMany(Schedule::class);
}

日程安排:

/**
 * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
 */
public function pub()
{
    return $this->belongsTo(Pub::class);
}

表明细表具有以下字段:

ID酒吧ID周日开业时间关门时间|

我使用以下函数来了解某个酒吧当前是否处于打开状态:

/**
 * @return bool
 */
public function isPubCurrentlyOpen()
{
    $schedules = Schedule::where([
            ['pub_id', $this->id ],
            ['week_day', Carbon::now()->dayOfWeek],
    ])->get();

    foreach ($schedules as $schedule){
        $isOpen[] =
            Carbon::now('Europe/Madrid')->between(
                Carbon::now('Europe/Madrid')->setTimeFromTimeString($schedule->opening_time),
                Carbon::now('Europe/Madrid')->setTimeFromTimeString($schedule->closing_time)
            );
    }

    if(in_array(true, $isOpen)){
        return true;
        //return "Pub Opened";
    }

    return false;
    //return "Pub Closed";
}

在我的pubcontroller中,当选择“filterbyopenpubs”选项时 if($request->openPubs == 1) ,仅显示已打开的酒馆 isOpen ==true .

知道模型之间的关系,我该怎么做?

我在找这样的东西:

 if($request->openPubs == 1)
 {
   $pubs = $pubs->with('pubSchedules')->where('isOpen' == true);
 }

你能帮助我吗?

谢谢!

5 回复 | 直到 7 年前

apokryfos 7 年前

你可以用“有”

$openPubs = Pub::whereHas('schedule', function ($query) {
       $query->where('week_day', Carbon::now()->dayOfWeek);
       $query->whereRaw(
           "'".Carbon::now('Europe/Madrid')->format("H:i:s")."' BETWEEN opening_time AND closing_time"
       ); 
})->get();

这是假设您的打开时间和关闭时间是适当的时间格式,而不是字符串(尽管字符串也可以在24小时格式下工作)。

通过使用范围(例如

public function scopeFilterBy($query, $filter = null) {
     if ($filter == "isOpen") {
        $query->whereHas('schedule', function ($query) {
          $query->where('week_day', Carbon::now()->dayOfWeek);
          $query->whereRaw(
            "'".Carbon::now('Europe/Madrid')->format("H:i:s")."' BETWEEN opening_time AND closing_time"
           );  
        });
    }
    return $query; //Not sure if this is needed
}

你可以这样做:

 Pub::filterBy($request->openPubs ? "isOpen" : null)->get();

Achraf Khouadja 7 年前

我不完全明白你是怎么做到的,但应该是这样的

   $pubs = Pub::with(['pubSchedules' => function ($query) {

        $query->where('opening_time', '>' ,Carbon::now()) // make sure it's currently open
              ->where('closing_time', '<' ,Carbon::now()) // make sure that it's not finished already
              ->where('week_day', '==' ,Carbon::now()->dayOfWeek) // make sure it's today

    }])->find($id);

  // to get if pub is currently

  if($pub->pubSchedules->count()){
      //
   }

   you can put this code in the model (Pub) and make some changes
   if you already have the object you can do this (Add it to model)

public function isPubOpen()
{
    $this->load(['pubSchedules' => 
        // same code in other method
    ]);

    return (bool) $this->pubSchedules->count();
}

Luis felipe De jesus Munoz 7 年前

对于小表,可以调用函数 isPubCurrentlyOpen 对于每个元素。

为此,您需要更改函数以接收pub_id作为参数:

public function isPubCurrentlyOpen($pub_id)
{
    $schedules = Schedule::where([
            ['pub_id', $pub_id ],
            ['week_day', Carbon::now()->dayOfWeek],
    ])->get();

    foreach ($schedules as $schedule){
        $isOpen[] =
            Carbon::now('Europe/Madrid')->between(
                Carbon::now('Europe/Madrid')->setTimeFromTimeString($schedule->opening_time),
                Carbon::now('Europe/Madrid')->setTimeFromTimeString($schedule->closing_time)
            );
    }

    if(in_array(true, $isOpen)){
        return true;
        //return "Pub Opened";
    }

    return false;
    //return "Pub Closed";
}

要查询数据,请执行以下操作:

if($request->openPubs == 1)
{
   // assuming $pubs is a collection instance
   $pubs = $pubs->filter(function($a){
        return $this->isPubCurrentlyOpen($a->id);
   })
}

Birendra Gurung 7 年前

在雄辩中有一个特征叫做 Eager Loading . 雄辩的orm提供了一个简单的语法来查询与此特定pub相关的所有计划,如下所述:

$pubIsOpen= $pub->schedules()
  ->where([
        ['week_day', Carbon::now()->dayOfWeek],
        ['opening_time' , '<' , Carbon::now('Europe/Madrid')],
        ['closing_time' , '>' , Carbon::now('Europe/Madrid')]
  ])
  ->count();
if($openPubCount > 0){
    //PUB is open
}else{
    //PUB is closed
}

Ber Tsacianegu del Tepuy 7 年前

如果对将来的人有帮助,我会发布我的解决方案,感谢@apokryfos:

Pub:

/**
 * @param $pubs
 * @return mixed
 */
public static function isPubCurrentlyOpen($pubs)
{
    $pubs->whereHas( 'pubSchedules', function ($pubs) {
        $pubs->where( 'week_day', Carbon::now()->dayOfWeek )
            ->whereRaw(
                "'" . Carbon::now( 'Europe/Madrid' )->format( "H:i:s" ) . "' BETWEEN opening_time AND closing_time"
            );
    } );

    return $pubs;
}

酒吧管理员:

/**
 * @param GetPubRequest $request
 * @return ApiResponse
 */
public function getPubs(GetPubRequest $request)
{
    $orderBy = 'id';
    $order = 'asc';

    $pubs = Pub::withDistance();

    ............


    if($request->openPubs == 1)
    {
        $pubs = Pub::isPubCurrentlyOpen($pubs);
    }

    return $this->response(PubProfileResource::collection($pubs->orderBy($orderBy, $order)->paginate()));
}