I have the following Eloquent query (This is a simplified version of a query which consists of of more where
s and orWhere
s hence the apparent roundabout way of going about this - the theory is what's important):
$start_date = //some date;
$prices = BenchmarkPrice::select('price_date', 'price')
->orderBy('price_date', 'ASC')
->where('ticker', $this->ticker)
->where(function($q) use ($start_date) {
// some wheres...
$q->orWhere(function($q2) use ($start_date){
$dateToCompare = BenchmarkPrice::select(DB::raw('min(price_date) as min_date'))
->where('price_date', '>=', $start_date)
->where('ticker', $this->ticker)
->pluck('min_date');
$q2->where('price_date', $dateToCompare);
});
})
->get();
正如你所看到的,我知道最早的日期是在我start_date
岁那年或之后.这将导致运行一个单独的查询来获取该日期,然后将该日期用作主查询中的参数.有没有一种方法可以Eloquent 地将查询嵌入到一起,形成一个子查询,从而只调用一个数据库调用,而不是两个?
Edit:
As per @Jarek's answer this is my query:
$prices = BenchmarkPrice::select('price_date', 'price')
->orderBy('price_date', 'ASC')
->where('ticker', $this->ticker)
->where(function($q) use ($start_date, $end_date, $last_day) {
if ($start_date) $q->where('price_date' ,'>=', $start_date);
if ($end_date) $q->where('price_date' ,'<=', $end_date);
if ($last_day) $q->where('price_date', DB::raw('LAST_DAY(price_date)'));
if ($start_date) $q->orWhere('price_date', '=', function($d) use ($start_date) {
// Get the earliest date on of after the start date
$d->selectRaw('min(price_date)')
->where('price_date', '>=', $start_date)
->where('ticker', $this->ticker);
});
if ($end_date) $q->orWhere('price_date', '=', function($d) use ($end_date) {
// Get the latest date on or before the end date
$d->selectRaw('max(price_date)')
->where('price_date', '<=', $end_date)
->where('ticker', $this->ticker);
});
});
$this->prices = $prices->remember($_ENV['LONG_CACHE_TIME'])->get();
这orWhere
个块导致查询中的所有参数突然变得不带引号.例如,WHERE
Price_date>= 2009-09-07
.当我删除orWheres
时,查询运行正常.这是为什么呢?