代码之家 › 专栏 › 技术社区 › rook99

获取用户所有付款的收入总和?

eloquent laravel

rook99 · 技术社区 · 5 年前

有很多书命令付款

书中型号:

public function payments()
{
   return $this->hasManyThrough(Payment::class, Order::class);
}

订单表

 Schema::create('orders', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('user_id');
            $table->unsignedBigInteger('book_id');
            $table->unsignedInteger('amount');

            $table->foreign('user_id')->references('id')->on('users');
            $table->foreign('book_id')->references('id')->on('books');
        });

付款迁移:

 Schema::create('payments', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('order_id');
            $table->string('payment_method');
            $table->unsignedInteger('author_earning');

            $table->foreign('order_id')->references('id')->on('orders');
        });
    }

我的问题是: 我想列出所有用户的总和 author_earning 所有属于他们帐簿的款项 我试过做一些类似的事情:

$users = User::with(['books.payments' => function ($query) {
            $query->sum('instructor_earning');
        }])
        ->whereHas('books.payments')
        ->get();

但是,运气不好。我怎么解决这个问题?

0 回复 | 直到 5 年前

TsaiKoga 5 年前

当你使用 ,闭包需要获取至少包含外键的雄辩构建器列,雄辩使用此外键来制作嵌套集合。

总和 ,不是雄辩的专栏;

所以如果你把每本书的作者收入加起来,你可以使用accessor,

像这样做,

protected $appends = ['author_earning'];

public function getAuthorEarningAttribute()
{
    $sum = Book::where('books.id', $this->id)
        ->join('orders', 'orders.book_id', '=', 'books.id')
        ->join('payments', 'payments.order_id', '=', 'orders.id')
        ->groupBy('books.id')
        ->selectRaw('books.id, SUM(payments.amount) AS sum')
        ->first()->sum;
    return $sum;
}

所以当你用这样的方式支付帐款时:

$users = User::with(['books.payments'])
        ->whereHas('books.payments')
        ->get()->toJson();

[{"id":1,
  "username":"Tom",
  "books":[{
    "id":1,
    "title": "Tom and Jerry",
    "author_earning": 18,
    "orders": [{
      "id":1,
      "number": "FA00000001"
      "payments": [{"id":1,"author_earning":15},{"id":2,"author_earning": 3}]
    },{
    "id":2,
    "title": "Dragon Ball",
    "author_earning": 2,
    "orders": [{
      "id":2,
      "number": "FA00000002"
      "payments": [{"id":1,"author_earning":2}]        
    }]
  }]
}]