Laravel Eloquent limit() and count() returns full count

Question

I need to use the count() method on the latest 1000 records.

Example, we have 3 users:

User A with 900 records
User B with 20.000 records
User C with 10 records

Now I want to consider only the latest 1000 records for my calculation, which means User A and C will use all their records, because < 1000, but User B uses only his last 1000 records and 19k are ignored.

// This should return for User A and C, 900 and 10, but for User C 1000
$recent_activities = Activity::select('id', 'user_id', 'activity')->where('user_id', $user->id)
->latest('id')->limit(1000)->count();



// The next 3 queries should analyse the recent 1000 records for occurences of certain %like% records and count them
$result_chat_refunds = Activity::where('activity', 'like', 'Credits erstattet%')->where('user_id', $user->id)
->latest('id')->limit(1000)->count();

$result_credit_membership = Activity::where('activity', 'like', '%Membership gutgeschrieben%')->where('user_id', $user->id)
->latest('id')->limit(1000)->count();

$result_credit_credits = Activity::where('activity', 'like', '%Credits gutgeschrieben%')->where('user_id', $user->id)
->latest('id')->limit(1000)->count();

How can I achieve this?

score 1 · Accepted Answer · answered Jul 19 '21 at 13:36

Here is a simple solution, which does not really use any additional tools from eloquent.

Seems to work really good and the performance is great aswell.

$combined_array = array();

$recent_activities = Activity::select('id', 'user_id', 'activity')->where('user_id', $user->id)
->latest('id')->limit(1000)->get();

$combined_array['number_total'] = count($recent_activities);
$count_chat_refunds = 0;
$count_credit_membership = 0;
$count_credit_credits = 0;
foreach($recent_activities as $cr) {
    if (str_starts_with($cr->activity, "Credits erstattet")) {
        $count_chat_refunds += 1;
    } else if (str_contains($cr->activity, "Membership gutgeschrieben")) {
        $count_credit_membership += 1;
    } else if (str_contains($cr->activity, "Credits gutgeschrieben")) {
        $count_credit_credits += 1;
    }
}

$combined_array['number_chat_refunds'] = $count_chat_refunds;
$combined_array['number_credit_membership'] = $count_credit_membership;
$combined_array['number_credit_credits'] = $count_credit_credits;

score 1 · Answer 2 · answered Jul 21 '21 at 15:54

1

Maybe a modification of this:

 SELECT TOP 1000 X, 
        COUNT(X) OVER() AS Y
 FROM TblZ
 WHERE Criteria ='Whatever'
 ORDER BY ID DESC

IDK I didn't test this so don't shoot me.

answered Jul 21 '21 at 15:54

Xyloz Quin

178
4

LordF · Answer 3 · 2021-07-19T14:29:14.500

The first time I did not understand the problem correctly:

$recentActivities = Activity::select('id')->where('user_id', $user->id)->latest('id')->limit(1000);

$resultChatRefunds = Activity::where('activity', 'like', 'Credits erstattet%')->whereIn('id', $recentActivities)->count();
$resultCreditMembership = Activity::where('activity', 'like', '%Membership gutgeschrieben%')->whereIn('id', $recentActivities)->count();
$resultCreditCredits = Activity::where('activity', 'like', '%Credits gutgeschrieben%')->whereIn('id', $recentActivities)->count();

Notice that limit with count is working in DB like your's results:

https://www.db-fiddle.com/f/tWDPuHtJsxiFYRv3XEFwH5/0

it's because the SELECT statement is running before the LIMIT, so DB first counts records and then limits it to 1000, but after counting you have a one record so there is nothing to limit.

This is wrong. If user C has done some of those activites way below his last 1000, then they still would count. — Roman, Jul 19 '21 at 13:14
Oh, so sorry. I did not understand the problem correctly. Check now, I've added another solution. — LordF, Jul 19 '21 at 14:30

Laravel Eloquent limit() and count() returns full count

3 Answers3