Postgres ->count() always returns 1 when grouping

[davzie]

• Laravel Version: 5.3.29
• PHP Version: 7.1
• Database Driver & Version: PostgreSQL 9.6.1 on x86_64-apple-darwin16.1.0, compiled by Apple LLVM version 8.0.0 (clang-800.0.42.1), 64-bit

Description:

Laravel will always return 1 when you try to call the ->count() method on a query that is being grouped. See below.

Steps To Reproduce:

If I want to get the amount of users in a system who have posted an article I might want to do something like this:

$userPostCount = DB::table('posts')
                                ->whereNotNull('user_id')
                                ->groupBy('user_id')
                                ->groupBy('id')
                                ->count();

But this will always return 1. At least whilst testing, I had 7 posts, each with a unique user_id but the query above will return 1.

As a result, I've had to get around this by doing:

$userPostCount = DB::table('posts')
                                ->whereNotNull('user_id')
                                ->groupBy('user_id')
                                ->groupBy('id')
                                ->select('user_id')
                                ->get()->unique('user_id')->count();

I don't mind doing this, but obviously at scale I'm pulling all records here and having to then count those which can't be too good. I would switch to MySQL but Postgres is kinda shitty about that.

I also posted this on #14123

[sisve]

Could you use the querylog/debugbar/Clockwork to check what actual sql statement is generated and executed?

[davzie]

@sisve I will do. I will also build a super simple example project and provide a ZIP of that for you.

[alfa6661]

i don't think this is laravel issue.
i think the problem is on your query.

why you need to group by the id? i believe the id is uniq each row.
even if you are using mySQL, i think the above query will return the same. CMIIW.

with the above code, your query will look like:

select count(*) as aggregate from `posts` where `user_id` is not null group by `user_id`, `id`

try to run the query and see the result.

[davzie]

@alfa6661 I may be wrong, I often am, but it appeared I couldn't actually run the query without grouping by ID. In any case, grouping by the ID shouldn't really do anything because they are all unique anyway. Postgres is very funny about this and for some reason requests it be done this way.

[movingSone]

@davzie Basically what you need to take notice about is this

$userPostCount = DB::table('posts')
                                ->whereNotNull('user_id')
                                ->groupBy('user_id')
                                ->groupBy('id')
                                ->count();

This line of code is doing next : Group Columns by user_id,id returns only one row -> which will always be 1 ( ** because each user when posts will have post with distinct id ** ), and when you call count() after that, Laravel will return you only one row. Which is always one because you are doing only counting on one row. That is why when you use get() and after that count() you are basically doing counting of SELECT for all rows.

Idea:
amount of users in a system who have posted an article
If you are looking only for only number of users who posted

$userPostCount = DB::table('posts')
            -select('user_id')
            ->whereNotNull('user_id')
            ->distinct()
            ->get()
            ->count('user_id');

No need for group by, you already know that distinct ids of users are in table.

[davzie]

@movingSone This is great. Does your final example not have the issue though that I'm still retrieving tonnes of rows from the database? What if there are 1,000,000 users, I'll be pulling 1,000,000 records from the DB and counting the result rather than harnessing the DB's built in counting functions.

[movingSone]

@davzie That will all depend on your implementation inside the system. If you will have million users then make testing of different methods. Good way to get speed would be to store that information in database where it is always populated when users create posts. That way you get results much faster.
Or try using
DB::select('custom SQL');