問題描述

我有以下 Eloquent 查詢(這是一個查詢的簡化版本，它由更多的 where 和 orWhere 組成，因此明顯的迂回方式這 - 理論很重要):

I have the following Eloquent query (This is a simplified version of a query which consists of of more wheres and orWheres 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();

如您所見，我選擇發(fā)生在我的start_date 或之后的最早日期.這會導(dǎo)致運行單獨的查詢以獲取此日期，然后將其用作主查詢中的參數(shù).有沒有一種雄辯的方法可以將查詢嵌入在一起形成一個子查詢，從而只有 1 個數(shù)據(jù)庫調(diào)用而不是 2 個?

As you can see I pluck the earliest date that occurs on or after my start_date. This results in a seperate query being run to get this date which is then used as a parameter in the main query. Is there a way in eloquent to embed the queries together to form a subquery and thus only 1 database call rather than 2?

根據(jù)@Jarek 的回答，這是我的查詢:

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 塊導(dǎo)致查詢中的所有參數(shù)突然變得不帶引號.例如.WHEREprice_date>= 2009-09-07.當(dāng)我刪除 orWheres 時，查詢工作正常.這是為什么?

The orWhere blocks are causing all parameters in the query to suddenly become unquoted. E.g. WHEREprice_date>= 2009-09-07. When I remove the orWheres the query works fine. Why is this?

推薦答案

這是您在以下位置執(zhí)行子查詢的方式:

This is how you do a subquery where:

$q->where('price_date', function($q) use ($start_date)
{
   $q->from('benchmarks_table_name')
    ->selectRaw('min(price_date)')
    ->where('price_date', '>=', $start_date)
    ->where('ticker', $this->ticker);
});

不幸的是orWhere需要明確提供$operator，否則會引發(fā)錯誤，所以在你的情況下:

Unfortunately orWhere requires explicitly provided $operator, otherwise it will raise an error, so in your case:

$q->orWhere('price_date', '=', function($q) use ($start_date)
{
   $q->from('benchmarks_table_name')
    ->selectRaw('min(price_date)')
    ->where('price_date', '>=', $start_date)
    ->where('ticker', $this->ticker);
});

<小時>

實際上你需要在閉包中指定from，否則它不會構(gòu)建正確的查詢.

You need to specify from in the closure in fact, otherwise it will not build correct query.

這篇關(guān)于如何使用 Laravel Eloquent 創(chuàng)建子查詢?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！