Laravel关联查询优化：无匹配时切换条件及subitems去重需求

Hey there! Let's work through your two requirements to adjust your existing query. You want unique ref_id values in your subitems, plus prioritize records where language_id = 1 (falling back to any language if no match exists). Here are a couple of solid approaches:

Approach 1: Sort + Group By (Simpler, Database-Specific) #

This method sorts subitems so that language_id = 1 records come first, then groups by ref_id to keep only the top (prioritized) record per ref_id.

$items = Item::with(['subitems' => function($query) {
    $query->select('subitems.*')
          // Prioritize language_id=1 by sorting it to the top
          ->orderByRaw('CASE WHEN language_id = 1 THEN 0 ELSE 1 END')
          // Group by ref_id to ensure uniqueness
          ->groupBy('ref_id');
}])->get();

Notes: #

• MySQL Users: If you hit an error about ONLY_FULL_GROUP_BY, you can either adjust your database config to disable this mode (not recommended for production) or tweak the select clause to use aggregate functions for non-grouped fields (e.g., MAX(id) instead of selecting all fields).
• PostgreSQL Users: This approach might run into stricter group-by rules—use Approach 2 instead for better compatibility.

Approach 2: Subquery to Fetch Prioritized IDs (More Robust) #

This method first identifies the correct subitem ID for each ref_id (prioritizing language_id=1), then fetches only those records. It’s database-agnostic and avoids group-by limitations.

$items = Item::with(['subitems' => function($query) {
    // Subquery to get the prioritized subitem ID per ref_id
    $prioritySubitemIds = \DB::table('subitems')
        ->selectRaw('
            COALESCE(
                MAX(CASE WHEN language_id = 1 THEN id END),  // Pick language_id=1 if exists
                MAX(id)  // Fallback to any other record if no match
            ) AS subitem_id
        ')
        ->groupBy('ref_id')
        ->pluck('subitem_id');

    // Only fetch subitems that match these prioritized IDs
    $query->whereIn('id', $prioritySubitemIds);
}])->get();

How It Works: #

• The subquery checks each ref_id: if there’s a record with language_id=1, it grabs that ID. If not, it takes the largest ID (swap MAX(id) with MIN(id) if you prefer a different fallback).
• The main query filters subitems to only include these pre-selected IDs, ensuring unique ref_ids and correct prioritization.

Verifying the Results #

After running either query, you can confirm:

• Every subitem in the results has a unique ref_id
• For any ref_id that has a language_id=1 record, that’s the one returned
• For ref_ids without a language_id=1 record, a valid fallback record is included

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