我在Lumen中创建一个 API ,我需要创建一个方法,在同一个表上获取 dates from two colums 并返回 any years that occur between those dates ,将它们全部作为单个数组返回 .
例如,假设一个名为 start_date
的表列和另一个名为 end_date
的表列
start_date | end_date getAllYears() should return =>
[1983, 1984, 1985, 1986, 1987,
1999, 2000, 2001, 2002, 2003,
..., 2016]
1999-05-09 | 2002-04-03
1983-03-12 | 1987-09-23
2001-02-12 | 2016-11-27
目前,我有它成功地做到这一点其他类型的更具体的查询方法,这种尝试的主要问题,是由于我是检索SQL记录,获得的大量这样的方法会导致我的请求时每一次出去 .
我的无效方法很少使用Lumen / Laravel
public function getAllYears(){
$dates = $this->model->select('data_ini', 'data_fim')->get();
$results = [];
foreach ($dates as $obj){
$carbonBegin = Carbon::createFromDate($obj->data_ini->year);
$carbonEnd = Carbon::createFromDate($obj->data_fim->year);
if($carbonEnd->year === 9999){
$carbonEnd->year = date('Y');
}
$carbonEnd->year++;
// Simple method that runs a DatePeriod method
$dateRange = $this->helper->createDateRange($carbonBegin, $carbonEnd);
$results = array_merge($results, $dateRange);
}
sort($results);
$cleanYears = array_unique($results);
if ($cleanYears == null)
return response()->json(['error' => true, 'errorCode' => '1008', 'message' => "No years found!"]);
else
return response()->json(['error' => false, 'years' => $cleanYears]);
}
所以,问题是,我怎么能以更便宜的方式做到这一点,以便我的服务器不会在每个请求上超时?提前感谢您的帮助:)
NOTE: DB:raw is a no-go as my TL has forbidden me from using it anywhere on the API
1 回答
看起来你需要whereBetween:
With models:
在上面,我正在利用MySQL的内置str_to_date
希望这可以帮助!