时间戳

首页 > 解决方案 > Laravel 6用不同的where子句查询同一张表

php - Laravel 6用不同的where子句查询同一张表

问题描述

我有这样的campaign_report桌子

Schema::create('campaign_report', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->date('report_date');
    $table->bigInteger('user_id');
    $table->bigInteger('campaignId');
    $table->double('cost',12,2);
    $table->timestamps();
});

我正在尝试获取所有昨天的竞选报告。

        $campaignReports = CampaignReport::where(['report_date' => "$yesterday", 'user_id' => Auth::user()->id])->orderBy('report_date', 'desc')->paginate(25);

上面的查询返回所有 report_date 等于 $yesterday 的活动。

除了这个查询，我还想获取前天与该列匹配的cost每个广告系列的值。我想展示昨天的广告系列和前天的广告系列之间的区别。report_datecampaignIdcost

喜欢

foreach($campaignReports as $campaignReport)
{
    $difference = $campaignReport->cost - $campaignReport->dayBeforeYesterdayCost; 
}

谁能帮助我使用 Query Builder 或 Eloquent 构建此查询？

标签： phpmysqllaravellaravel-6laravel-query-builder

解决方案

您可以在CampaignReport模型中编写一个关系来计算昨天成本和前一天之间的差异

public function dayBeforeYesterdayCost() {
     $dayBeforeYesterday = Carbon::parse($this->report_date)->subDays(1); //return date of day before yesterday

     $dayBeforeYesterdayCost = CampaignReport::where('campaignId', $this->campaignId)
     ->where('report_date', $dayBeforeYesterday)
     ->first();

     return $dayBeforeYesterdayCost ? $dayBeforeYesterdayCost->cost : 0;
}


public function lastDayDifference() {
     $difference = $this->cost - $this->dayBeforeYesterdayCost; 
     return $difference;
}

现在你可以返回这样$campaignReports的lastDayDifference关系：

 $campaignReports = CampaignReport::where(['report_date' => "$yesterday", 'user_id' => Auth::user()->id])
->with('lastDayDifference')
->orderBy('report_date', 'desc')
->paginate(25);

最后，您的每一个对象都附有一个对象$campaignReport

推荐阅读