英文:
How to group by the amount of values in an array in postgresql
问题
我有一个帖子表,其中包含几个列,包括一个liked_by列,其类型为int数组。
由于我无法在这里发布表格,我将发布一个单个帖子的JSON结构,如下所示:
"post": {
"ID": 1,
"CreatedAt": "2022-08-15T11:06:44.386954+05:30",
"UpdatedAt": "2022-08-15T11:06:44.386954+05:30",
"DeletedAt": null,
"title": "Pofst1131",
"postText": "yyhfgwegfewgewwegwegwegweg",
"img": "fegjegwegwg.com",
"userName": "AthfanFasee",
"likedBy": [
3,
1,
4
],
"createdBy": 1,
}
我正在尝试按照喜欢的顺序发送帖子(最受欢迎的帖子)。这将根据liked_by数组中的值的数量对帖子进行排序。我如何在Postgres中实现这一点?
顺便说一下,我正在使用Go语言和GORM ORM,但我正在使用原始的SQL构建器而不是ORM工具。我也可以使用Go语言解决这个问题。在MongoDB和NodeJS中,我实现这一点的方式是按liked by数组的大小进行分组,并添加一个总喜欢计数字段,并使用该字段进行排序,如下所示:
if(sort === 'likesCount') {
data = Post.aggregate([
{
$addFields: {
totalLikesCount: { $size: "$likedBy" }
}
}
])
data = data.sort('-totalLikesCount');
} else {
data = data.sort('-createdAt') ;
}
英文:
I have a posts table with few columns including a liked_by column which's type is an int array.
As I can't post the table here I'll post a single post's JSON structure which comes as below
"post": {
"ID": 1,
"CreatedAt": "2022-08-15T11:06:44.386954+05:30",
"UpdatedAt": "2022-08-15T11:06:44.386954+05:30",
"DeletedAt": null,
"title": "Pofst1131",
"postText": "yyhfgwegfewgewwegwegwegweg",
"img": "fegjegwegwg.com",
"userName": "AthfanFasee",
"likedBy": [
3,
1,
4
],
"createdBy": 1,
}
I'm trying to send posts in the order they are liked (Most Liked Posts). Which should order the posts according to the number of values inside the liked_by array. How can I achieve this in Postgres?
For a side note, I'm using Go lang with GORM ORM but I'm using raw SQL builder instead of ORM tools. I'll be fine with solving this problem using go lang as well. The way I achieved this in MongoDB and NodeJS is to group by the size of liked by array and add a total like count field and sort using that field as below
if(sort === 'likesCount') {
data = Post.aggregate([
{
$addFields: {
totalLikesCount: { $size: "$likedBy" }
}
}
])
data = data.sort('-totalLikesCount');
} else {
data = data.sort('-createdAt') ;
}
答案1
得分: 2
使用本地查询。
假设包含示例数据的表列名为 post
,那么查询语句如下:
select <表达式列表> from the_table
order by json_array_length(post->'likedBy') desc;
无关的问题,为什么不尝试使用规范化的数据设计?
编辑
现在我知道你的表结构了,以下是更新后的查询语句。使用 array_length。
select <表达式列表> from public.posts
order by array_length(liked_by, 1) desc nulls last;
你也可以添加一个 where
子句。
英文:
Use a native query.
Provided that the table column that contains the sample data is called post
, then
select <list of expressions> from the_table
order by json_array_length(post->'likedBy') desc;
Unrelated but why don't you try a normalized data design?
Edit
Now that I know your table structure here is the updated query. Use array_length.
select <list of expressions> from public.posts
order by array_length(liked_by, 1) desc nulls last;
You may also wish to add a where
clause too.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论