[FIXED] Cakephp get associative sum group by in postgres

Issue

I have two tables

coins       summary_coins 
--------    -------------
id | name    id| date                |  get_count | coin_id
===|====    ==============================================
 1 |lira     1 | 2020-02-16 16:55:50 |  20        | 1
 2 |A        1 | 2020-03-16 16:55:50 |  12        | 1
 3 |B        1 | 2020-03-16 16:55:50 |  20        | 1

My Expected result

name get_count   previous_month_count  
Lira 32          20

I have tried below cakephp method to get expected result

    public function getCount($start_date,$end_date,$previous_month_start_date,$previous_month_end_date)
    {
        $query = $this->find();
        $query
            ->select([
                        'Coins.id',
                        'Coins.name',
                        'curr_get_count' => $query->func()->sum('SummaryCoins.get_count'),
                        'prev_get_count' => $query->func()->sum('SummaryCoins.get_count'),
 
            ])
            ->matching('SummaryCoins')
            ->where([
                'SummaryCoins.user_id' => 1,
                'SummaryCoins.date >' => '2021-04-01',
                'SummaryCoins.date <' => '2021-05-31'
            ])
            ->group([
                'Coins.id',
             ]);
        return $query;
   }

Here I am getting present month range, how I will get previous month count ?

Solution

There's many ways to achieve this, subqueries, multiple custom joins, case expressions, etc...

I would suggest that you try case expressions first, where depending on your date conditions you either return SummaryCoins.get_count, NULL (which is being ignored by SUM) or 0 (in case you want the sum to be 0 instead of NULL if no rows are matching your conditions), eg generate SQL like:

SUM(
    CASE WHEN
        SummaryCoins.date >= '2020-03-01' AND
        SummaryCoins.date <= '2020-03-31'
    THEN
        SummaryCoins.get_count
    ELSE
        0
    END
)

$query
    ->select([
        'Coins.id',
        'Coins.name',
        'curr_get_count' => $query->func()->sum(
            $query
                ->newExpr()
                ->addCase(
                    [
                        $query->newExpr()->add([
                            'SummaryCoins.date >=' => $start_date,
                            'SummaryCoins.date <=' => $end_date,
                        ])
                    ],
                    [$query->identifier('SummaryCoins.get_count'), 0]
                    [null, 'integer']
                )
        ),
        'prev_get_count' => $query->func()->sum(
            $query
                ->newExpr()
                ->addCase(
                    [
                        $query->newExpr()->add([
                            'SummaryCoins.date >=' => $previous_month_start_date,
                            'SummaryCoins.date <=' => $previous_month_end_date,
                        ]),
                    ],
                    [$query->identifier('SummaryCoins.get_count'), 0]
                    [null, 'integer']
                )
        ),
    ])

Also note that you should use >= and <= to make the conditions inclusive, otherwise the first day and the last day of the month would be ignored in case the time part is 00:00:00.

See also

• Cookbook > Database Access & SQL > Query Builder > Case Statements

Answered By - ndm