Laravel关联查询用Join后OrderBy失效，求助实现指定排序逻辑

Hey there, let's break down why your sorting logic isn't working and fix it up properly! I see you're trying to prioritize bills where boosted is true, then sort the rest by the latest funds creation date—but your current query has a couple of issues that are blocking this from working as expected.

Let's first spot the problems in your original code: #

• The orderBy(DB::raw('bills.boosted', 'asc')) line is written incorrectly. DB::raw() only takes a single parameter, so your sort direction (asc) isn't being applied at all. Also, to get boosted=true bills first, you actually want to sort this field in descending order (since boolean values are stored as 1/0 in most databases—descending puts 1s first).
• When you groupBy('bills.id','funds.id'), you're grouping by both bill and fund IDs, which means you'll get a separate row for every bill-fund pair. This not only creates duplicate bill entries but also messes up your sorting, since the database can't properly apply your order across grouped pairs.
• You're selecting all fields from both tables (bills.*, funds.*), which in strict database modes (like MySQL's ONLY_FULL_GROUP_BY) will cause errors, and doesn't help you target the latest fund creation date per bill.

Solution 1: Fix the Join & GroupBy Approach #

If you want to stick with a join, adjust the query to group by bill ID only, grab the latest fund creation date for each bill, then apply your sorting correctly:

$bill = Bill::with(['relation1', 'relation2'])
    ->where('category_id', $category)
    ->where('status_id', 2)
    ->join('funds', 'bills.id', '=', 'funds.bill_id')
    // Select bill data + the latest fund creation date for each bill
    ->select('bills.*', DB::raw('MAX(funds.created_at) as latest_funds_created_at'))
    // Group by bill ID to get one row per bill
    ->groupBy('bills.id')
    // Sort: boosted bills first, then newest fund dates first
    ->orderBy('bills.boosted', 'desc')
    ->orderBy('latest_funds_created_at', 'desc')
    ->take(8)
    ->get();

dd($bill);

Solution 2: Use a Subquery (Cleaner & More Reliable) #

A better approach is to avoid joins altogether and use a subquery to fetch the latest fund creation date for each bill. This keeps your query cleaner and avoids groupBy-related issues:

$bill = Bill::with(['relation1', 'relation2'])
    ->where('category_id', $category)
    ->where('status_id', 2)
    // Add a subquery to get the latest fund creation date for each bill
    ->addSelect([
        'latest_funds_created_at' => Fund::select('created_at')
            ->whereColumn('bill_id', 'bills.id')
            ->orderBy('created_at', 'desc')
            ->limit(1)
    ])
    // Apply your desired sort order
    ->orderBy('bills.boosted', 'desc')
    ->orderBy('latest_funds_created_at', 'desc')
    ->take(8)
    ->get();

dd($bill);

Why These Work: #

• Both solutions first prioritize bills where boosted is true by sorting that field in descending order.
• They then sort the remaining bills by the latest fund creation date (not just any fund date), which aligns with your requirement.
• The subquery approach (Solution 2) is especially reliable because it doesn't rely on groupBy, so you won't run into strict database mode errors, and it clearly associates each bill with its most recent fund entry.

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