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.

[jyamada]

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.

Thank you

Unfortunately I can't use the advanced pack for budget reasons.

The data returns this

{"keyList":["2026-01","2026-02","2026-03","2026-04","2026-05","2026-06","2026-07","2026-08"],
"dataMap":{"6a6bb1ba9826be583":{"2026-07":5,"2026-08":8.3,"2026-01":0,"2026-02":0,"2026-03":0,"2026-04":0,"2026-05":0,"2026-06":0,"2026-09":0,"2026-10":0,"2026-11":0,"2026-12":0},
"6a84ae3b4143a3c91":{"2026-07":7,"2026-08":2,"2026-01":0,"2026-02":0,"2026-03":0,"2026-04":0,"2026-05":0,"2026-06":0,"2026-09":0,"2026-10":0,"2026-11":0,"2026-12":0}}}

So the month range, and the user id with an array of sums by month. Based off the sales by month js, I think I know what needs to be done in the prepareData function, two foreach loops to set things in the right places. But my js is rusty, if you can do it faster than I can, that would be greatly appreciated. Not urgent.

Also how do I get the user names after I get the ids?

[yuri]

You can fetch entity by an ID. https://docs.espocrm.com/development...xisting-entity

Then get the 'name' attribute. https://docs.espocrm.com/development...ttribute-value

Not the most performant way but simple.

[jyamada]

You can fetch entity by an ID. https://docs.espocrm.com/development...xisting-entity

Then get the 'name' attribute. https://docs.espocrm.com/development...ttribute-value

Not the most performant way but simple.

Thank you.


Extremely close, but the stacking isn't stacking. series: {stack:true}, and stack:true in the data passed in.

Json. It's not sorted, I'm not sure if that's an issue.

{"keyList":["2026-01","2026-02","2026-03","2026-04","2026-05","2026-06","2026-07","2026-08"],
"dataMap":{"Admin":{"2026-07":5,"2026-08":8.3,"2026-01":0,"2026-02":0,"2026-03":0,"2026-04":0,"2026-05":0,"2026-06":0,"2026-09":0,"2026-10":0,"2026-11":0,"2026-12":0},
"Dum E":{"2026-07":7,"2026-08":2,"2026-01":0,"2026-02":0,"2026-03":0,"2026-04":0,"2026-05":0,"2026-06":0,"2026-09":0,"2026-10":0,"2026-11":0,"2026-12":0}}}

Also it seems like something is wrong with the width

            var tickNumber = this.getTickNumber();
            this.flotr.draw(this.$container.get(0), this.chartData, {
                shadowSize: false,
                bars: {
                    show: true,
                    horizontal: false,
                    shadowSize: 0,
                    lineWidth: 1 * this.fontSizeFactor,
                    fillOpacity: 1,
                    barWidth: 0.5,
                },
                grid: {
                    horizontalLines: true,
                    verticalLines: false,
                    outline: 'sw',
                    color: this.gridColor,
                    tickColor: this.tickColor,
                },
                yaxis: {
                    min: 0,
                    showLabels: true,
                    color: this.textColor,
                    max: this.max + 0.08 * this.max,
                    tickFormatter: (value) => {
                        value =  parseFloat(value);
                        if (!value) {
                            return '';
                        }
                        if (value % 1 === 0) {
                            return '<span class="numeric-text">' +
                                this.formatNumber(Math.floor(value), false, true).toString() + '</span>';
                        }

                        return '';
                    },
                },
                xaxis: {
                    min: 0,
                    color: this.textColor,
                    noTicks: tickNumber,
                    tickFormatter: (value) => {
                        if (value % 1 === 0) {
                            let i = parseInt(value);
                            if (i in this.monthList) {
                                if (this.monthList.length - tickNumber > 5 && i === this.monthList.length - 1) {
                                    return '';
                                }
                                return moment(this.monthList[i] + '-01').format('MMM YYYY');
                            }
                        }
                        return '';
                    }
                },
                series: {
                    stack: true,
                },
                stack: true,
                mouse: {
                    track: true,
                    relative: true,
                    lineColor: this.hoverColor,
                    position: 's',
                    autoPositionVertical: true,
                    trackFormatter: obj => {
                        let i = parseInt(obj.x);
                        let value = '';
                        if (i in this.monthList) {
                            value += moment(this.monthList[i] + '-01').format('MMM YYYY') + '<br>';
                        }
                        return obj.series.label.toString() + "<br>"+ value +
                            '<span class="numeric-text">' + this.formatNumber(obj.y, true) + '</span>';
                    }
                },
            })

[jyamada]

Update, I missed something very obvious. It's stackED, not stack.

The bars are wider than they should be, despite xaxis saying they are all the same (including january which is the right width)

[jyamada]

You can fetch entity by an ID. https://docs.espocrm.com/development...xisting-entity

Then get the 'name' attribute. https://docs.espocrm.com/development...ttribute-value

Not the most performant way but simple.

One more problem


The graph width isn't correct. The graph code in a test environment works fine, so I think it's something about the container.

What confuses me is that Jan looks fine, but the rest extend to the right more than they should, but their options don't show any obvious change.



FINAL EDIT: I was sending the data as "2", not 2. Just javascript things.