在Eloquent数据库Laravel中按最大值搜索

Question

如何在我的API控制器中将此Raw SQL转换为Eloquent数据库？

我所尝试的是，我想从我的API控制器的两个表中获取数据。

第一：Formc_image表，它是关于数据的一般信息

第二：Formc_image_detail，它是数据的细节和路径

它们通过ID进行关联。

这是我的API控制器FormCController.php

SELECT * 
FROM formc_image_detail
WHERE id_tps=$id_tps AND jenis_pemilihan=$election_id
AND versi = (SELECT MAX(versi) FROM formc_image WHERE id_tps=id_tps AND jenis_pemilihan=$election_id)
ORDER BY no_lembar ASC

这是我的模型FormCImageDetail

<?php
namespace AppModels;
use IlluminateDatabaseEloquentModel;
class FormCImageDetail extends Model
{
protected $table = 'formc_image_detail';
public function formc_image(){
return $this->belongsTo('AppModelsFormCImage');
}
}

这是我的FormCImage型号

<?php
namespace AppModels;
use IlluminateDatabaseEloquentModel;
class FormCImage extends Model
{
protected $table = 'formc_image';
}

我在我的API控制器：中制作了这个代码

return response(FormCImageDetail::with('formc_image')
->where('jenis_pemilihan', $electionId)
->where('id_tps', $tpsId)
->orderBy('no_lembar', 'ASC')->paginate(100)->jsonSerialize(), Response::HTTP_OK);

但它仍然是错误的。

这是我的迁移：

Schema::create('formc_image', function (Blueprint $table) {
$table->integer('id_tps');
$table->smallint('versi');
$table->string('jenis_pemilihan');
$table->timestamps();
}
Schema::create('formc_image_detail', function (Blueprint $table) {
$table->integer('id_tps');
$table->smallint('versi');
$table->integer('no_lembar');
$table->string('jenis_pemilihan');
$table->char('url_image', 100);
$table->timestamps();
}

Answer 1

使用此：

return FormCImageDetail::with('formc_image')
->where('jenis_pemilihan', $electionId)
->where('id_tps', $tpsId)
->where('versi', function($query) use($election_id) {
$query->select(DB::raw('max(versi)'))
->from('formc_image')
->whereColumn('id_tps', 'formc_image_detail.id_tps')
->where('jenis_pemilihan', $election_id);
})
->orderBy('no_lembar')
->paginate(100);