SQL sum by assigned user

[jyamada]

Last issue for a while, I promise. So I copied over the sales by month graph logic, and it works. But now I want to stack the bars by user. I'm not exactly sure how flotr wants that data but right now I can't get sql to cooperate.

The original was:

        $whereClause[] = [
            'collectionDate>=' => $from->toString(),
            'collectionDate<' => $to->toString(),
        ];

        $queryBuilder
            ->select([
                ['MONTH:collectionDate', 'month'],
                ['SUM:gallonsCollected', 'gallonsCollected']
            ])
            ->order('MONTH:collectionDate')
            ->group('MONTH:collectionDate')
            ->where($whereClause);

And that correctly gets the sum of all gallons. But I want it split up by user. Trying ->group('assignedUserName') throws an error saying the column doesn't exist. For sanity check requesting select * shows that it does exist.

            ->select([
                ['MONTH:collectionDate', 'month'],
                ['gallonsCollected', 'gallonsCollected'],
                ['assignedUserName', 'assignedUserName']    
            ])
            ->order('MONTH:collectionDate')
            ->group('MONTH:collectionDate')
            ->where($whereClause);​​

That doesn't throw an error, but only returns the first entry in the month.

Worst case scenario I take select * and sort the entries on the php side, but that seems silly. There must be something I'm missing.

[yuri]

You need to group by `assignedUserId`. Then, you can load user names separately. Or join users in a single query.

[eymen-elkum]

Yuri’s suggestion is the important part here: `assignedUserName` is not the field you should group by. Group the results by both the month and `assignedUserId`, then resolve the user names separately or through a join.

Before continuing with a fully custom implementation, it is also worth reviewing EspoCRM Advanced Pack. Its Reports feature and report dashlets are powerful and may already cover this requirement, or at least reduce the amount of custom code that must be maintained. Whenever possible, using the official solution is preferable because it receives ongoing support and stays aligned with future EspoCRM updates.

If the required chart or data structure is not supported by Advanced Pack, an illustrative custom query would look like this:

->select([
    ['MONTH:collectionDate', 'month'],
    'assignedUserId',
    ['SUM:gallonsCollected', 'gallonsCollected'],
])
->group([
    'MONTH:collectionDate',
    'assignedUserId',
])
->order('MONTH:collectionDate')

This should return one row for every month/user combination.

You can then reshape the result into one Flotr series per user. Each series would contain points in the form:

[monthIndex, gallonsCollected]

Make sure to insert a zero value when a user has no records for a particular month. Otherwise, the points in the stacked series may not align correctly between users.

The final data could conceptually look like this:

[
    {
        label: 'User A',
        data: [[1, 120], [2, 80], [3, 0]]
    },
    {
        label: 'User B',
        data: [[1, 50], [2, 110], [3, 75]]
    }
]

The exact query-builder syntax may need a small adjustment depending on how your current repository query is constructed, but the essential part is grouping by both `MONTH:collectionDate` and `assignedUserId`.

I would first compare the requirement with the reporting and dashlet capabilities available in Advanced Pack. If a custom chart is still necessary, share a sample of the returned rows and the current dashlet JavaScript, and I can help verify the exact Flotr data structure. We can also assist with implementing the custom dashlet when the official reporting options do not cover the required use case.