Laravel Eloquent limit and distinct

@niloleon

so i need to extract real 99 (not counting records with equal "Claims_id")

can you supply an example of what you are trying to extract?

Ok

+------------+-------------+ 
| claims_id | num_pre |  
+------------+-------------+ 
|          1 |       200 |         
+------------+-------------+ 
|          1 |        200 |      
+------------+-------------+ 
|          1 |        200 |        
+------------+-------------+ 
|          2 |        201 |          
+------------+-------------+ 
|          4 |       202 |        
+------------+-------------+ 
|          3 |       203 |      

Imagine num_pre is initially null then i want to insert a num_pre starting with 200, but selecting $limite = 6 I will generate only 4 because i have 3 records with equal claims_id. So i need initially to extract more then 6 records because records with claims_id=1 count as 1

ok. so what would extracting 'real 99' return?

could you add another column to mark that the record is the first for that claim and then you could filter for all first claims (which is what I think you might be wanting to do)

Try this:

$limite = 10;

$claims = Refunds::query()
    ->select(['claims_id'])
    ->distinct()
    ->whereNull('num_pre')
    ->where('status_ref', '>', 4)
    ->orderBy('claims_id')
    ->offset(0)
    ->limit($limite)
    ->pluck('claims_id'); // will get only the values

foreach ($claims as $index => $claimId) {
    // use same where clauses
    Refunds::query()
        ->whereNull('num_pre')
        ->where('status_ref', '>', 4)
        ->where('claims_id', $claimId) // add this one
        ->update(['num_pre' => 200 + $index]);
    // num_pre needs to be in $fillable property
    // or the model should have no guarded fields
}

Instead of using foreach you can use ->each():

$index = 0;
    
Application::query()
    ->select(['claims_id'])
    ->distinct()
    ->whereNull('num_pre')
    ->where('status_ref', '>', 4)
    ->orderBy('claims_id')
    ->offset(0)
    ->limit($limite)
    // needs to close $index by reference
    // as the closure mutates its value
    ->each(function ($claimId) use (&$index) {
        Refunds::query()
            ->whereNull('num_pre')
            ->where('status_ref', '>', 4)
            ->where('claims_id', $claimId)
            ->update(['num_pre' => 200 + ($index++)]);
    });

Alternative: If you need to select them prior you can use joinSub as MySQL won't let you use limit and offset in a "where in" subquery:

$limite = 100;

$refunds = Application::query()
    ->joinSub(function ($query) use ($limite) {
        $query->select(['claim_id'])
            ->distinct()
            ->from('refunds')
            ->whereNull('num_pre')
            ->where('status_ref', '>', 4)
            ->orderBy('claims_id')
            ->offset(0)
            ->limit($limite);
    }, 'claims', 'claims.claim_id', '=', 'refunds.claim_id')
    ->whereNull('num_pre')
    ->where('status_ref', '>', 4)
    ->orderBy('refunds.claims_id')
    ->get();

$claimIds = $refunds->pluck('claims_id')->unique()->values();

foreach ($claimIds as $index => $claimId) {
    Refunds::query()
        ->whereNull('num_pre')
        ->where('status_ref', '>', 4)
        ->where('claims_id', $claimId)
        ->update(['num_pre' => 200 + $index]);
}

Hope it helps