mysql - 获取belongsToMany关联中的最后一条记录

Question

我在约会和状态模型之间有一个belongsToMany关联。以下查询返回所有状态，我想更改它以提取分配给约会的最后状态。

$query = Appointment::query();
            $query->with('statuses');
            $query->with("agent");
            $query->with("instruction_type");
            $query->with("sign_up_customer");
            $table = Datatables::of($query);

我尝试用它来更改查询，但它不起作用。

$query->with('statuses')->latest();

这是我的原始查询:

select * from `users` where `users`.`id` = '1' limit 1
select count(*) as aggregate from (select '1' as `row_count` from `appointments`) count_row_table

select * from `appointments` limit 100 offset 0

select `statuses`.*, `appointment_status`.`appointment_id` as `pivot_appointment_id`, `appointment_status`.`status_id` as `pivot_status_id` from `statuses` inner join `appointment_status` on `statuses`.`id` = `appointment_status`.`status_id` where `appointment_status`.`appointment_id` in ('2') order by `created_at` desc

select * from `agents` where `agents`.`id` in ('1')

select * from `instruction_types` where `instruction_types`.`id` in ('1')

select * from `organisations` where `organisations`.`id` in ('1')

所以这可行，但它对状态运行两个查询

$query = Appointment::with(['statuses' => function ($query) {
                $query->latest()->first();
            }]);
            $query->with("agent");
            $query->with("instruction_type");
            $query->with("sign_up_customer");
            $table = Datatables::of($query);

Answer 1

为了从数据库中获取最新记录，您应该有一个 created_at 列来实现这一点。在这种情况下，你可以这样做:

Appointment::with(['statuses' => function ($query) {
    $query->orderBy('created_at', 'desc')->first();
}])->get();