Count does not work as expected in Laravel Query Builder

Question

I am trying to get how many names do I have in database. For this purpose I am using Query Builder like this:

$namesIdsCount = DB::table('names_to_options')
    ->select('name_id')
    ->groupBy('name_id')
    ->havingRaw($having)
    ->count();

Is says that 24, which is not correct, because if I will write code like this:

$namesIdsCount = DB::table('names_to_options')
    ->select('name_id')
    ->groupBy('name_id')
    ->havingRaw($having)
    ->get();

result object contains 247 elements, which is correct. I have tried to play with skip/take, but still no results. Where am I wrong? Thanks for any help.

@Jerodev it contains my custom sql like SUM(CASE WHEN option_id IN ({$optionSql}) THEN 1 ELSE 0 END) > 0 AND SUM(CASE WHEN option_id IN ({$optionSql}) THEN 1 ELSE 0 END) > 0, where $optionSql is a string with ids separated with coma. — Vittore Gravano
– Vittore Gravano, Commented Nov 9, 2018 at 9:45
first run the query with get() method, then count the values in the $namesIdsCount collection, i, e. count($namesIdsCount) — Ali
– Ali, Commented Nov 9, 2018 at 10:19
My first guess would be that the groupBy results in 24 groups, what Ali said, what is the count($namesIdsCount)? — Inuyaki
– Inuyaki, Commented Nov 9, 2018 at 10:29

kmuenkel · Accepted Answer · 2018-11-12 21:52:38Z

I think it's the other way around, you're not getting 24 groups. You're getting 24 elements within the first group. That configuration results in the following query:

SELECT
    COUNT(*) AS 'aggregate',
    `name_id`
FROM `names_to_options`
WHERE EXISTS(
    {your $havingRaw sub-query}
)
GROUP BY `name_id`;

What you end up with will look something like this:

+---------------+---------+
| aggregate     | name_id |
+---------------+---------+
| 24            | 1       |
+---------------+---------+
| 5             | 2       |
+---------------+---------+
| 30            | 3       |
+---------------+---------+
| ... and so on | 4       |
+---------------+---------+

Query\Builder just doesn't realize you can get more than one result back when count() is involved.

You were pretty close to the right answer yourself though.

$namesIdsCount = DB::table('names_to_options')
    ->select('name_id')
    ->groupBy('name_id')
    ->havingRaw($having)
    ->get();

get() returns an Eloquent\Collection, child of Support\Collection, which has its own version of the count method. So your answer is just:

$namesIdsCount = DB::table('names_to_options')
    ->select('name_id')
    ->groupBy('name_id')
    ->havingRaw($having)
    ->get()
    ->count();

If you really want this to happen in MySQL, the query you want to happen would look like this:

SELECT COUNT(*) FROM (
    SELECT
        `name_id`
    FROM `names_to_options`
    WHERE EXISTS(
        {your $havingRaw sub-query}
    )
    GROUP BY `name_id`
) AS temp;

For that, you can do this:

$query = DB::table('names_to_options')
    ->select('name_id')
    ->groupBy('name_id')
    ->havingRaw($having);
$sql = $query->toSql();
$values = $query->getBindings();
$count = DB::table(DB::raw('('.$sql.') AS `temp`'))
    ->selectRaw("COUNT(*) AS 'aggregate'", $values)
    ->first()
    ->aggregate;

MySQL performance can get a little hairy when asking it to write temp-tables like that though, so you'll have to experiment to see which option is faster.

This is absolutely correct, thank you so much for explaining it to me!

Osama Alvi · Accepted Answer · 2018-11-09 12:13:03Z

0

Inuyaki is right

(id, name_id),

(1,1),

(2,1),

(3,2),

(4,3)

There are are four rows so get() method will return 4 rows

but there are three groups if you use groupBy [name_id]

1 (1,1)

2 (2)

3 (3)

now count will return 3

hope this will help.

answered Nov 9, 2018 at 12:13

Osama Alvi

6794 silver badges15 bronze badges

Collectives™ on Stack Overflow

Count does not work as expected in Laravel Query Builder

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related