Laravel Eloquent如何按type_id分别获取指定数量的数据？

Hey there! The problem with your current code is that whereIn('type_id', [2,4])->take(8) doesn’t guarantee exactly 4 rows for each type_id—it might pull more of one type if there are more entries available. Here are a few straightforward, Laravel-native ways to get the exact result you need:

Method 1: Separate Queries + Collection Merge #

The simplest approach is to fetch rows for each type_id individually, then merge the results into a single collection. It’s easy to read and maintain:

// Grab 4 items where type_id = 2
$type2Items = DB::table('table')
    ->where('category_id', $category)
    ->where('type_id', 2)
    ->take(4)
    ->get();

// Grab 4 items where type_id = 4
$type4Items = DB::table('table')
    ->where('category_id', $category)
    ->where('type_id', 4)
    ->take(4)
    ->get();

// Merge the two collections into one
$wish = $type2Items->merge($type4Items);

If you’re using an Eloquent model (like Table), just swap DB::table('table') with Table::—the rest of the logic stays identical.

Method 2: Database-Level UNION #

You can combine the two queries at the database level using union, which executes a single merged query:

// Define subquery for type_id = 2
$queryType2 = DB::table('table')
    ->where('category_id', $category)
    ->where('type_id', 2)
    ->take(4);

// Define subquery for type_id = 4
$queryType4 = DB::table('table')
    ->where('category_id', $category)
    ->where('type_id', 4)
    ->take(4);

// Union the queries and fetch results
$wish = $queryType2->union($queryType4)->get();

This returns a single collection with combined results, just like the first method, but it’s handled in one database round trip.

Method 3: Scalable Approach for Multiple type_ids #

If you might add more type_id and count pairs later, wrap the logic in a loop for better scalability:

// Map type_ids to the number of rows you want
$typeCounts = [
    2 => 4,
    4 => 4
    // Add more pairs here as needed
];

$wish = collect();

foreach ($typeCounts as $typeId => $count) {
    $items = DB::table('table')
        ->where('category_id', $category)
        ->where('type_id', $typeId)
        ->take($count)
        ->get();
    
    $wish = $wish->merge($items);
}

This way, you won’t have to rewrite query logic every time you add a new type_id requirement.

All these methods ensure you get exactly 4 rows for type_id=2 and 4 rows for type_id=4—something your original code couldn’t guarantee.

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