Nested relationships with ordering and pagination

Hi all,

I'm trying build an achievements system using the simplified database schema and models listed at the bottom of this post. I've hit a bit of a roadblock when figuring out how to retrieve a user's unlocked achievements while ordering them and paginating.

The following is an example of my desired result to be outputted:

Category 1
  - Sequence 1
    - Achievement 1
    - Achievement 2
    - Achievement 3
  - Sequence 2
    - Achievement 4
    - Achievement 5
Category 2
  - Sequence 3
    - Achievement 6

The categories should be ordered by name, the sequences within a category should also be ordered by name, the achievements within a sequence should be ordered by level, and finally the result should be paginated by n sequences per page. Only achievements where progress unlocked at is not null should be included.

The following seems to work fine in terms of getting every achievement in the desired fashion (since I can simply output the category name by comparing current/previous category name when iterating through the sequences). However I'm trying to get the result based on the user's unlocked status rather than all achievements in the system.

AchievementSequence::query()
    ->join('achievement_categories', 'achievement_sequences.achievement_category_id', '=', 'achievement_categories.id')
    ->with([
        'achievements' => fn ($query) => $query->orderByDesc('level'),
        'achievementCategory'
    ])
    ->orderBy('achievement_categories.name')
    ->orderBy('achievement_sequences.name')
    ->paginate(5, 'achievement_sequences.*');

DB Schema: https://i.imgur.com/jWI2USz.png

// User.php
class User extends Authenticatable
{
    public function achievementProgress(): HasMany
    {
        return $this->hasMany(AchievementProgress::class);
    }
}

// AchievementCategory.php
class AchievementCategory extends Model
{
    public function achievementSequences(): HasMany
    {
        return $this->hasMany(AchievementSequence::class);
    }
}

// AchievementSequence.php
class AchievementSequence extends Model
{
    public function achievementCategory(): BelongsTo
    {
        return $this->belongsTo(AchievementCategory::class);
    }

    public function achievements(): HasMany
    {
        return $this->hasMany(Achievement::class);
    }
}

// Achievement.php
class Achievement extends Model
{
    public function achievementSequence(): BelongsTo
    {
        return $this->belongsTo(AchievementSequence::class);
    }

    public function progress(): HasMany
    {
        return $this->hasMany(AchievementProgress::class);
    }
}

// AchievementProgress.php
class AchievementProgress extends Model
{
    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class);
    }

    public function achievement(): BelongsTo
    {
        return $this->belongsTo(Achievement::class);
    }
}