Laravel Eloquent Query - limit on a count()

Question

I need to know if a table has more than 50 rows fitting some criteria. So, the raw query should look like this:

SELECT COUNT(id) FROM (SELECT id FROM table WHERE {conditions} LIMIT 50)

but I'm having trouble doing this through eloquent. This is what I've tried so far....

card::where( ... )->limit(50)->count("id");

... but this doesn't work. It doesn't make the subquery for the limit, so the limit becomes useless.

Without running a subquery that is limited, the query takes up to 10 times longer..... I'm afraid it won't be as scalable as I need.

See here: http://stackoverflow.com/questions/15229303/is-there-a-way-to-limit-the-result-with-eloquent-orm-of-laravel — Tim Biegeleisen, Jan 29 '17 at 08:20

score 1 · Accepted Answer · answered Jul 08 '19 at 08:39

1

I did eventually come up with a solution to this, I just didn't post it (until now). You can just grab the ID of the 50th record to see if it exists:

$atLeastFifty = Card::offset(50)->value("id");

if(!empty($atLeastFifty)){
    // there are at least 50 records
}else{
    // there are less than 50 records
}

This is way faster than a count() when there are tons of records in the table.

answered Jul 08 '19 at 08:39

Skeets

4,476
2
41
67

1

Nice trick, should be the accepted answer IMO. – Chibueze Opata Aug 31 '21 at 08:13
Good point, since the other answer doesn't really answer the question. I've made this the accepted answer. – Skeets Sep 01 '21 at 05:03

score 0 · Answer 2 · answered Jan 29 '17 at 08:25

0

If you just want to count number of columns, use count() without arguments:

$numberOfRows = Card::where(....)->take(50)->count();

answered Jan 29 '17 at 08:25

Alexey Mezenin

158,981
26
290
279

unfortunately it seems that `take(50)` has the *same effect* as `limit(50)` when coupled with `count()`. The query as revealed by the logs actually doesn't change at all :P (the query is still `select count(*) as aggregate from table where ... limit 50` instead of using a subquery) – Skeets Jan 29 '17 at 08:31
1

@That's how Eloquent and Query Builder do it. You need to use raw queries if this is not suitable for you. – Alexey Mezenin Jan 29 '17 at 08:33

Laravel Eloquent Query - limit on a count()

2 Answers2