Laravel多对多关联中如何按中间表Timestamp排序？

当前在Laravel项目中，原本按Url模型的时间戳排序，现在需要改为按User与Url多对多关联的中间表时间戳排序，尝试多种方法未成功，以下是模型及分页查询代码，求排查解决：

模型代码 #

Url 模型 #

class Url extends Model
{
    use HasFactory;
    // ... 其他代码 ...
    public function users()
    {
        return $this->belongsToMany(User::class)->withTimestamps();
    }
    public function hostname()
    {
        return $this->belongsTo(Hostname::class);
    }   
}

User 模型 #

class User extends Authenticatable implements JWTSubject
{
    use HasApiTokens, HasFactory, Notifiable;
    // ... 其他代码 ...
    public function getJWTIdentifier()
    {
        return $this->getKey();
    }
    public function getJWTCustomClaims()
    {
        return [];
    }
    public function urls()
    {
        return $this->belongsToMany(Url::class)
            ->withTimestamps();
    }
}

仓库分页查询代码 #

public function paginate() 
{ 
    return Url::query()
        ->with(['users'=>function($query)
        {
            $query
                ->select('id', 'username', 'slug')
                ->where([
                    'active'=>true
                ]);
        }])
        ->with(['hostname'=>function($query)
        {
            $query
                ->select('id', 'hostname', 'slug')
                ->where([
                    'active'=>true
                ]);
        }])
        ->select('id', 'url', 'title', 'hostname_id', 'updated_at')
        ->where([
            'active'=>true
        ])
        ->orderByDesc('created_at') // <-- 此处需要替换为中间表时间戳排序
        ->paginate();
}

解决思路 #

思路1：关联表JOIN后排序 #

要基于中间表时间戳排序，需先将Url表与中间表（默认名为user_url，自定义表名请替换）关联，再按中间表的时间戳字段排序：

public function paginate() 
{ 
    return Url::query()
        ->with(['users'=>function($query)
        {
            $query
                ->select('id', 'username', 'slug')
                ->where([
                    'active'=>true
                ]);
        }])
        ->with(['hostname'=>function($query)
        {
            $query
                ->select('id', 'hostname', 'slug')
                ->where([
                    'active'=>true
                ]);
        }])
        ->select('urls.id', 'urls.url', 'urls.title', 'urls.hostname_id', 'urls.updated_at')
        ->join('user_url', 'urls.id', '=', 'user_url.url_id')
        ->where([
            'urls.active'=>true,
            // 若需限定当前用户关联的Url，添加此条件避免重复
            'user_url.user_id' => auth()->id() 
        ])
        ->orderByDesc('user_url.created_at') // 按需选择中间表的created_at或updated_at
        ->distinct() // JOIN后可能出现重复Url，必须去重
        ->paginate();
}

思路2：从User模型出发查询关联Url #

如果是基于当前用户的关联时间戳，直接从User模型查询关联Url，可更简洁地使用中间表排序：

public function paginate() 
{ 
    return auth()->user()->urls()
        ->with(['hostname'=>function($query)
        {
            $query
                ->select('id', 'hostname', 'slug')
                ->where([
                    'active'=>true
                ]);
        }])
        ->select('urls.id', 'urls.url', 'urls.title', 'urls.hostname_id', 'urls.updated_at')
        ->where([
            'urls.active'=>true
        ])
        ->orderByDesc('user_url.created_at') // 中间表时间戳字段
        ->paginate();
}

注意事项 #

• 中间表默认名称为两个模型复数按字母顺序拼接（user_url），若自定义表名，需在belongsToMany方法中指定（如belongsToMany(User::class, 'custom_table_name')），排序时也要用自定义表名。
• 若不限定用户，JOIN后必须用distinct()去重，否则同一个Url关联多个用户会产生重复记录。
• 确保中间表存在时间戳字段（因使用了withTimestamps()，迁移文件中应已添加created_at和updated_at）。

内容的提问来源于stack exchange，提问作者Les