Laravel中orderBy与paginate函数联用排序失效问题

Hey there! It looks like you're trying to sort either the related user records attached to each Scan, or sort the entire Scan list based on user-specific fields—but right now your orderBy('scan.created_at', 'desc') is only sorting the Scan entries themselves, not the user data you're eager loading. Let's break down the solutions based on what you're actually trying to achieve:

Case 1: Sort the related User records for each Scan #

If you want the user associated with each Scan to be sorted (especially useful if it's a one-to-many relationship, like a Scan linked to multiple users), you need to add a sorting closure inside the with() method to target the user association directly:

$scan_data = Scan::where(['profile' => 'public'])
    ->with(['user' => function ($query) {
        // Sort users by their own created_at (or any other user field) in descending order
        $query->orderBy('created_at', 'desc');
        // Or use the shorthand for created_at sorting:
        // $query->latest();
    }])
    ->whereHas('user')
    ->orderBy('scan.created_at', 'desc') // This still sorts the Scan entries themselves
    ->paginate();

This ensures that when user records are loaded for each Scan, they're ordered exactly how you want.

Case 2: Sort the entire Scan list based on a User field #

If your goal is to reorder the Scan results using a field from the associated User (e.g., show Scans from the most recently created users first), you'll need to either join the users table or use a subquery in your orderBy clause:

Option A: Using a Join #

$scan_data = Scan::select('scans.*')
    ->join('users', 'scans.user_id', '=', 'users.id') // Replace with your actual foreign key
    ->where(['scans.profile' => 'public'])
    ->whereNotNull('users.id') // Same as whereHas('user') to ensure a user exists
    ->orderBy('users.created_at', 'desc') // Sort Scans by the user's creation date
    ->paginate();

Option B: Using a Subquery (No Join Needed) #

$scan_data = Scan::where(['profile' => 'public'])
    ->with(['user'])
    ->whereHas('user')
    ->orderByRaw('(SELECT created_at FROM users WHERE users.id = scans.user_id) DESC')
    ->paginate();

Why Your Original Code Didn't Work #

Your original orderBy('scan.created_at', 'desc') only affects the order of the Scan records in the result set. It doesn't touch the order of the related user data, nor does it sort Scans based on user attributes—those require targeting the users table directly as shown above.

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