Cakephp在postgres中通过获取关联和组

Question

我有两个表

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

我的预期结果

name get_count   previous_month_count  
Lira 32          20

我已经尝试了下面的cakepp方法来获得预期的结果

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;
}

这里我得到的是本月的范围，我将如何得到上月的计数？

Answer 1

有很多方法可以实现这一点，子查询、多个自定义联接、事例表达式等

我建议您先尝试大小写表达式，根据您的日期条件，您可以返回SummaryCoins.get_count、NULL(被SUM忽略(或0(如果没有与您的条件匹配的行，则您希望总和为0而不是NULL(，例如生成类似SQL的：

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']
)
),
])

还要注意，您应该使用>=和<=使条件包含在内，否则，如果时间部分是00:00:00，则会忽略该月的第一天和最后一天。

另请参见

• 食谱>数据库访问&SQL>查询生成器>案例陈述