Count not working if you use join and group by

[mauthi]

This is a bug.

Prerequisites

• Are you running the latest version?
• Are you reporting to the correct repository?
• Did you check the documentation?
• Did you perform a cursory search?

Description

I use the following query for a table.

return CustomerGroup::select(\DB::raw('
            customer_groups.id as "dtRowId", name, description, COUNT(customers.id) as customer_count
        '))
	    	->leftJoin('customers', 'customers.customer_group_id', '=', 'customer_groups.id')
	    	->groupBy('customer_groups.id');

Expected behavior

Result should be a list of customer_groups and a column with count of customers.
Count query should look like:

select
  count(*) as aggregate
from
  `customer_groups`

where
  `customer_groups`.`deleted_at` is null

Actual behavior

Problem is, that the count query looks like:

select
  count(*) as aggregate
from
  `customer_groups`
  left join `customers` on `customers`.`customer_group_id` = `customer_groups`.`id`
where
  `customer_groups`.`deleted_at` is null
group by
  `customer_groups`.`id`

As result I get a list of customer count for customer groups and not a single result with count of customer groups.

Please feel free to ask if my description is not clear.

[aocneanu]

This is not a bug, it's how the query builder works when using count with group by in Laravel.

Did you perform a cursory search?
No :P

But I did it for you

[mauthi]

Thx for your answer! To be honest I did no search since I am following the issues but I missed this somehow :)

Back to my problem: I am not happy with both suggested solutions in the other issue :( And I don’t know a better way to solve this in db design. I am thinking of either using a view on db layer and selecting from view or using a subquery in select (maybe a mysql function). Thx for your help in the meantime.

[aocneanu]

Well, you can also try another approach using withCount.

return CustomerGroup::withCount('customers')
    ->selectRaw('
            customer_groups.id as "dtRowId", name, description, COUNT(customers.id) as customer_count
     ');

and add in your customerGroups.json template a new field:

{
        "label": "Customers",
        "name": "customers_count",
        "data": "customers_count",
        "meta": []
}, 

Note that this column can't be sortable nor searchable.

[mauthi]

Thx - it works.
Sidenote: You don't need COUNT(customers.id) as customer_count in raw select, it's built automatically (with column name name customers_count)

In future it would be nice to make columns both sort and searchable.

Maybe one approach for that:

• add a config option for mysql_function in which we can define a function name or a calculation
• if used, the built query should look like this:

SELECT
	customer_groups.id,
	customer_groups.`name`,
	count( customers.id ) AS customers_count,
	sum( customers.id ) AS customers_sum,
	customer_groups.id * 2 AS customer_group_id_multi_2 
FROM
	`customer_groups`
	LEFT JOIN `customers` ON `customers`.`customer_group_id` = `customer_groups`.`id` 
WHERE
	`customer_groups`.`deleted_at` IS NULL 
GROUP BY
	`customer_groups`.`id` 
HAVING
	count( customers.id ) > 3 
	AND sum( customers.id ) < 1000 
ORDER BY
	count( customers.id ) DESC,
	sum( customers.id ) ASC 
LIMIT 0,3

With that approach we don't change anything on original behaviour but could help users to built e.g. tables for simple statistics (sortable and searchable).

What do you think about this?

[aocneanu]

You don't need COUNT(customers.id) as customer_count in raw select, it's built

Of course. Not only that you don't need it but the query won't work. I didn't pay enough attention when I pasted the query.

add a config option for mysql_function in which we can define a function name or a calculation

Please elaborate on this

As stated in the issue linked above, in my experience I've never came across a situation where I specifically needed to use GroupBy in a data table. In fact, I don't believe that I used a group by in any projects developed with Laravel.

[mauthi]

As stated in the issue linked above, in my experience I've never came across a situation where I specifically needed to use GroupBy in a data table. In fact, I don't believe that I used a group by in any projects developed with Laravel.

In my use cases using count() is also an edge case but using e.g. sum or small calculations would help me a lot.

Use cases:

• Show total net revenue of all customers (if have customers ... 1-n ... invoices structure)
• Show gross amount of an invoice (net_amount * 1.2)
• and of course it would be nice to sort customers by total net revenue

But I didn't get deep enough in your code to get an idea how we can provide this functionality.

[aocneanu]

Will close for now until we will have a PR or something more concrete.

[robbykrlos]

Hi there,

We had the same issue with the count:

Table::selectRaw('
    table1.id,
    table1.name as TableName,
    GROUP_CONCAT(DISTINCT table2.name) as "table2Name",
    table1.type,
	table2.name
')
    ->leftJoin('.....')
    ->groupBy(
        'table1.name'
    ) 

And according to
laravel/ideas#1693
laravel/framework#22883
and what @aocneanu said, it is indeed a "problem" with how Laravel query builder works.

We went for this work-around:

laravel-enso\tables\src\app\Services\Data\Builders\Meta.php

Replace:

return $this->query->count(); 

With:

return $this->query->getQuery()->getCountForPagination(); 

and by "replace" I mean by binding local class that overwrites only the count() method from Meta.