thinkphp 5.1 的 with hasOne belongTo 关联用不了排序

如果需求为只查询一个用户的 创建时间小于等于 “2022-12-31 23:59:59″的且创建时间为最大的订单时,可以这样实现.

只查一个用户的还是比较容易

#订单模型类型
class Order extends Model {}
#用户模型类型
class User extends Model {

    //查一个查不排序了，改为一对多
    public function orders()
    {
        return $this->hasMany(Order::class,'user_id','id');
    }

    //一对一查询不可排序,所以不用这个了
    public function order()
    {
        return $this->hasOne(Order::class,'user_id','id');
    }
}


$where = [
    ['id','=',1],
];
$with = [
    'orders' => function ($query) {
        $query->where('created_at','<=','2022-12-31 23:59:59')->order('created_at','desc')->limit(1);//查1条代替一对一关联
    }
];
$user = User::where($where)->with($with)->find();
//如果存在 下标为0的 那条就是结果了
dump($user->orders);

如果是查多个用户，且带上 创建时间小于等于 “2022-12-31 23:59:59″的且创建时间为最大的订单，就比较麻烦了

//条件
$where = [];
$limit = 100;
$users = User::where($where)->order('id','desc')->paginate($limit);
if ($users->count() > 0) {
    $userIds = $users->column('id');
    //订单条件
    $where = [
        ['user_id','IN',$userIds],
        ['created_at','<=','2022-12-31 23:59:59'],
    ];
    $field = ['user_id','MAX(created_at) AS max_created_at'];
    $order = [
        'created_at' => 'desc',
        'id'         => 'desc',
    ];
    //group by 也是不会排序的，查出user_id 和最大时间 再联表
    $sql = Order::where($where)->field($field)
                ->group('user_id')->buildSql();

    $where = [
        ['t1.user_id','IN',$userIds],
        ['t2.created_at','<=','2022-12-31 23:59:59'],
    ];
    $on = 't1.created_at = t2.created_at';
    // user_id => '订单数据' 的格式
    $oders = Order::alias('t1')->join("(${$sql}) t2",$on)->where($where)->column('*','user_id');
    //用户数据拼接订单数据 
    foreach ($users as $user) {
        $user->order = isset($orders[$user['id']]) ? $orders[$user['id']] : null;
    }
    
}