用laravel中的avg连接两个查询

Question

我有这个：

$avgOfCourse = StudyArgument::where('course_id', '=', 2)->select(DB::raw('date avgDate ,round(avg(study),1) avgStudy , round(avg(argument),1) avgArgument'))->groupBy('date')->get();

结果是：

avgDate       |   avgStudy  |   avgArgument
1992-08-16    |   250.0     |   250.0
2022-01-10    |   150.0     |   200.0
2022-02-03    |   350.0     |   350.0

然后我有了这个：

$studyArgumentOfUser = DB::table('study_arguments', 'users')->where('user_id', '=', Auth::user()->id)->select('date', 'study', 'argument')->get();

结果是

date          |   study |   argument
2022-01-10    |   200   |   300
2022-01-01    |   150   |   150
1992-08-16    |   250   |   250
2022-02-03    |   350   |   350

现在我想合并这两个查询像这样：

avgDate       |   avgStudy  |   avgArgument  |   study |   argument
1992-08-16    |   250.0     |   250.0       |   250   |   250
2022-01-10    |   150.0     |   200.0       |   200   |   300
2022-02-03    |   350.0     |   350.0       |   350   |   350

如何从上述查询中获得此结果？

Answer 1

Schema::create('study_arguments', function (Blueprint $table) {             $table->id();             $table->unsignedBigInteger('user_id');             $table->foreign('user_id')->references('id')->on('users')->onUpdate('cascade');             $table->unsignedBigInteger('course_id');             $table->foreign('course_id')->references('id')->on('courses')-onUpdate('cascade');             $table->integer('study')->nullable();             $table->integer('argument')->nullable();             $table->date('date');             $table->timestamps();         });

Schema::create('users', function (Blueprint $table) {              $table->id();             $table->string('name');             $table->string('email')->unique();             $table->timestamp('email_verified_at')->nullable();             $table->string('password');             $table->rememberToken();             $table->timestamps();         });