一种更好，干净的方式来在关系之间做到这一点

Question

我正在进行API开发，并且我得到了正在寻找的结果，但我认为不是在某些之间进行此代码的干净和最佳方法人际关系，并想阅读一种更好的方法。

我正在寻找两个日期和一个用户的定时轨道的结果。

我们有三个表/型号： - 用户 - 旅行(带字段用户_id( - 轨道(带有field trip_id(

重要的是：轨道表在每一行上有两个日期，'start_datetime'和'stop_dateTime'。

我的代码：

// We get all the 'trip_id' from the dates range
$alltracks = Track::whereBetween('start_datetime', [$datefrom, $dateto])->orWhereBetween('stop_datetime', [$datefrom, $dateto])->pluck('trip_id')->toArray();
// We get all the 'trips_id' of the user $id on the range
$trips = Trip::where('user_id', $id)->whereIn('id', $alltracks)->pluck('id')->toArray();
// If we have results, we check again to get the right tracks
if ($trips != null) {
    $tracks = Track::whereBetween('start_datetime', [$datefrom, $dateto])
    ->orWhereBetween('stop_datetime', [$datefrom, $dateto])
    ->whereIn('trip_id', $trips)
    ->orderBy('start_datetime', 'ASC')->get();
} else {
    return response()->json(['status' => 404, 'message' => 'There are no trips on this range for this driver'], 404);
}

我得到了我要寻找的结果，但这让我疯狂为结果做3个查询。

Answer 1

您可以在一个查询中使用内部联接来进行此操作。要在Laravel使用连接，您可以使用DB立面。

//Using the DB facade
use IlluminateSupportFacadesDB;

获取想要的曲目：

$tracks = DB::table('tracks')
              ->select('tracks.*','trips.*') //select needed columns
              ->join('trips','trips.id','=','tracks.trip_id')
              ->whereBetween('tracks.start_datetime', [$datefrom, $dateto])
              ->orWhereBetween('tracks.stop_datetime', [$datefrom, $dateto])
              ->where('trips.user_id',$id)
              ->orderBy('tracks.start_datetime', 'ASC')
              ->get();

get方法返回一个Illuminate support collection，其中包含结果，每个结果都是PHP STDCLASS对象的实例。

Answer 2

假设您在表之间有这种关系

User -> hasMany -> Trips -> hasMany -> Tracks

然后您可以尝试此操作(未进行测试，但想法是这样(：

# AppUser.php
public function trips()
{
    return $this->hasMany(Trip::class);
}
# AppTrip.php
public function user()
{
    return $this->belongsTo(User::class);
}
public function tracks()
{
    return $this->hasMany(Track::class);
}
# AppTrack.php
public function trip()
{
    return $this->belongsTo(Trip::class);
}
# SomethingController.php
public function findByUser($user_id)
{
    $datefrom = ...
    $dateto = ...
    $tracks = Track::whereBetween('start_datetime', [$datefrom, $dateto])
        ->orWhereBetween('stop_datetime', [$datefrom, $dateto])
        ->with('trip.user' => function($qry) use ($user_id) {
            $qry->where('id' => $user_id);
        })->get();
    // now you have the tracks
    // additionally to get the trips, see below
    $trips = [];
    if (!$tracks->isEmpty()) {
        $trips = $tracks->map(function($track) {
            return $track->trip;
        });
    }
    ....
}

Answer 3

这是HasmanyThrough雄辩关系的好用例。您可以尝试一下：

在User模型上定义hasManyThrough关系：

public function tracks()
{
     return $this->hasManyThrough(Track::class, Trip::class);
}

在Controller中：

User::whereId($id)->first()
   ->tracks()
     ->whereBetween('tracks.start_datetime', [$datefrom, $dateto])
     ->orWhereBetween('tracks.stop_datetime', [$datefrom, $dateto])
     ->orderBy('tracks.start_datetime', 'ASC')
   ->get();
// It gives you all the tracks taken by the underlying user
// within the defined conditions

希望它有帮助！