我有以下查询
engagement_metrics = EngagementMetric.where(engagement_id: engagement_ids).order('metrics_date desc').limit(7).group_by { |p| p.metrics_date }
结果是这样的
{
"2016-05-13": [
{
"id": 4,
"provider": "facebook",
"likes": -2,
"comments": 0,
"shares": 0,
"views": 0,
"reach": 0,
"reactions": {
"sad_count": "0",
"wow_count": "-1",
"haha_count": "0",
"like_count": "-1",
"love_count": "0",
"angry_count": "0"
}
},
{
"id": 5,
"provider": "facebook",
"likes": 2,
"comments": 2,
"shares": 2,
"views": 2,
"reach": 0,
"reactions": {
"sad_count": "0",
"wow_count": "0",
"haha_count": "0",
"like_count": "0",
"love_count": "0",
"angry_count": "0"
}
}
],
"2016-05-12": [
{
"id": 3,
"provider": "facebook",
"likes": 1,
"comments": 3,
"shares": 0,
"views": 0,
"reach": 0,
"reactions": {
"sad_count": "1",
"wow_count": "0",
"haha_count": "0",
"like_count": "0",
"love_count": "0",
"angry_count": "0"
},
"engagement_id": 1,
"participation_id": 1,
"campaign_id": 1,
"influencer_authorization_id": 1,
"influencer_id": 1,
"social_account_id": 1,
"metrics_date": "2016-05-12",
"status": "processed",
"deleted_at": null,
"created_at": "2016-05-14T11:36:55.995Z",
"updated_at": "2016-05-14T11:36:55.995Z"
}
],
"2016-05-11": [
{
"id": 2,
"provider": "facebook",
"likes": 0,
"comments": 16,
"shares": 0,
"views": 0,
"reach": 0,
"reactions": {
"sad_count": "0",
"wow_count": "0",
"haha_count": "0",
"like_count": "0",
"love_count": "0",
"angry_count": "0"
}
}
],
"2016-05-10": [
{
"id": 1,
"provider": "facebook",
"likes": 3,
"comments": 4,
"shares": 0,
"views": 0,
"reach": 0,
"reactions": {
"sad_count": "0",
"wow_count": "1",
"haha_count": "0",
"like_count": "1",
"love_count": "1",
"angry_count": "0"
}
}
]
}
这是迭代获取数据的最佳方法，如下所示
[
{
"date": "24/03/16",
"metrics": {
"likes_count": "29",
"comments_count": "456",
"shares_count": "234",
"views_count": "65",
"clicks_count": "123"
}
},
{
"date": "25/03/16",
"metrics": {
"likes_count": "345",
"comments_count": "234",
"shares_count": "876",
"views_count": "345",
"clicks_count": "45"
}
},
{
"date": "26/03/16",
"metrics": {
"likes_count": "345",
"comments_count": "265",
"shares_count": "243",
"views_count": "165",
"clicks_count": "87"
}
},
{
"date": "27/03/16",
"metrics": {
"likes_count": "376",
"comments_count": "87",
"shares_count": "54",
"views_count": "754",
"clicks_count": "34"
}
},
{
"date": "28/03/16",
"metrics": {
"likes_count": "103",
"comments_count": "324",
"shares_count": "405",
"views_count": "87",
"clicks_count": "354"
}
},
{
"date": "29/03/16",
"metrics": {
"likes_count": "23",
"comments_count": "65",
"shares_count": "234",
"views_count": "87",
"clicks_count": "34"
}
},
{
"date": "30/03/16",
"metrics": {
"likes_count": "98",
"comments_count": "576",
"shares_count": "34",
"views_count": "365",
"clicks_count": "212"
}
}
]
出于性能方面的考虑，您应始终尽力将数据库分组（使用group），而不是ruby代码（group_by）。我认为可以使用自定义选择和分组来检索您所要求的内容，即给定列的每日总和：
EngagementMetric.
select("metrics_date as date").
select("sum(likes) as likes_count").
select("sum(comments) as comments_count").
select("sum(shares) as shares_count").
select("sum(views) as views_count").
select("sum(clicks) as clicks_count").
where(engagement_id: engagement_ids).
group("date").
order("date desc").
to_json
# => [{ date: "2016-05-01", likes_count: 123, comments_count: 456, ... }, {...}]
即，这将汇总每日分组中的所有数据并返回结果。如果您希望指标总和.appear出现在“ metrics”子键下（您可以使用自定义as_json方法来完成），则只需构造一些不同的JSON 。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句