postgresql Postgres：使用同一行中的值填充列

agyaoht7  于 2022-11-04  发布在  PostgreSQL
关注(0)|答案(1)|浏览(110)

试图通过同一行上的查询来填充对原始“共享”帖子的引用。看起来其他select语句工作得很好，但self-reflected on语句却不行。
查询如下所示

SELECT 
    posts.*,
    (
        SELECT to_jsonb(users)
        FROM myapp.users 
        WHERE users.id = posts."userId"
    ) as user,
    // !! Breaking here ///
    (
        SELECT to_jsonb(posts)
        FROM myapp.posts 
        WHERE posts.id = posts."originalPostId"
    ) as "originalSharedPost"
FROM myapp.posts

如您所见，用户已填写，但原始共享帖子未填写。x1c 0d1x

Data
[
    {
        "id": "z1aN2n1tnqJAhW3-",
        "markdown": "test",
        "trendingScore": 10,
        "likes": 0,
        "dislikes": 0,
        "shares": 0,
        "views": 10,
        "originalPostId": null,
        "recipeId": null,
        "wasEdited": 0,
        "medais": [
        ],
        "hashTags": "{}",
        "ipAddressOfPost": "176.111.206.130",
        "location": {
            "city": "Oslo",
            "country": "NO",
            "isRough": true,
            "latLong": [
                59.8468,
                10.7747
            ],
            "longName": "NO",
            "timezone": "Europe/Oslo",
            "shortName": "Oslo"
        },
        "i18nLanguagesDetected": "{en}",
        "createdAt": "2022-10-27 09:56:23.733+02",
        "updatedAt": "2022-10-27 10:36:21.428+02",
        "userId": "ckEQtm",
        "reports": 0,
        "recipe": null,
        "user": {
            "id": "ckEQtm",
            "bio": "",
            "name": "Olly",
            "isBanned": false,
            "location": {
                "city": "Oslo",
                "country": "NO",
                "isRough": true,
                "latLong": [
                    59.8468,
                    10.7747
                ],
                "longName": "NO",
                "timezone": "Europe/Oslo",
                "shortName": "Oslo"
            },
            "createdAt": "2022-10-25T12:27:18.364+02:00",
            "isDeleted": false,
            "isFlagged": false,
            "isVisable": true,
            "updatedAt": "2022-10-27T10:56:41.802+02:00",
            "profileUrl": {
                "id": "L9RtfueTEWU",
                "url": "https://storage.googleapis.com/myapp-dev/uploads/ckEQtm/images/QufsJIMvt_f9axN3E",
                "name": "me-nice-bg.jpg",
                "mimeType": "image/jpeg",
                "originalUrl": "https://storage.googleapis.com/myapp-dev/uploads/ckEQtm/images/QufsJIMvt_f9axN3E_original"
            },
            "phoneNumber": "+1234455",
            "recipeViews": 0,
            "profileViews": 0,
            "followerCount": 1,
            "trendingScore": 1,
            "followingCount": 1,
            "ipAddressOfUser": "176.111.206.130",
            "isPlatformAdmin": false,
            "isCompanyEnabled": false,
            "lastOpenedAppIso": "2022-10-27T10:56:41.801+02:00",
            "recipeTrendingScore": 1
        },
        "originalSharedPost": null
    },
    {
        "id": "nI0aVB92PT1oRKDy",
        "markdown": "Check this recipe out",
        "trendingScore": 11,
        "likes": 0,
        "dislikes": 0,
        "shares": 0,
        "views": 11,
        "originalPostId": "z1aN2n1tnqJAhW3-",
        "recipeId": "LyvIGDaDK",
        "wasEdited": 0,
        "medais": [
            {
                "id": "32_fxP3_G9n",
                "url": "https://storage.googleapis.com/myapp-dev/dummy_profile_photos/profile_default_1.jpg",
                "name": "test",
                "mimeType": "image/jpeg",
                "originalUrl": null
            }
        ],
        "hashTags": "{}",
        "ipAddressOfPost": "84.211.160.110",
        "location": {
            "city": "Fredrikstad",
            "country": "NO",
            "isRough": true,
            "latLong": [
                59.2198,
                10.9247
            ],
            "longName": "NO",
            "timezone": "Europe/Oslo",
            "shortName": "Fredrikstad"
        },
        "i18nLanguagesDetected": "{en}",
        "createdAt": "2022-10-27 09:56:31.96+02",
        "updatedAt": "2022-10-27 10:36:23.713+02",
        "userId": "ckEQtm",
        "reports": 0,
        "recipe": {
            "id": "LyvIGDaDK",
            "name": "Avocado Toast",
            "views": 1,
            "medias": [
                {
                    "id": "32_fxP3_G9n",
                    "url": "https://storage.googleapis.com/myapp-dev/dummy_profile_photos/profile_default_1.jpg",
                    "name": "test",
                    "mimeType": "image/jpeg"
                }
            ],
            "userId": "ckEQtm",
            "location": {
                "city": "Fredrikstad",
                "country": "NO",
                "isRough": true,
                "latLong": [
                    59.2198,
                    10.9247
                ],
                "longName": "NO",
                "timezone": "Europe/Oslo",
                "shortName": "Fredrikstad"
            },
            "createdAt": "2022-10-25T12:27:58.761+02:00",
            "updatedAt": "2022-10-25T12:27:58.761+02:00",
            "categories": [
                "midnight-snack"
            ],
            "description": "The best meal",
            "ingredients": [
                {
                    "ingredientId": "By_2yVOjr",
                    "amountAndInfo": "1 Avocado",
                    "weightInGrams": 215,
                    "ingredientName": "Avocado"
                },
                {
                    "ingredientId": "JL7hQeZfy",
                    "amountAndInfo": "1 Slice",
                    "weightInGrams": 30,
                    "ingredientName": "1 Slice - Toasted Multigrain Bread"
                },
                {
                    "ingredientId": "w0SywAGZR",
                    "amountAndInfo": "Spicy for the USA",
                    "weightInGrams": 0.05,
                    "ingredientName": "Chilli flakes"
                }
            ],
            "cookingSteps": [
                {
                    "id": "xKknDJktuuo",
                    "media": {
                        "id": "ggPZMXVDHjb",
                        "url": "https://storage.googleapis.com/myapp-dev/dummy_profile_photos/profile_default_1.jpg",
                        "name": "test",
                        "mimeType": "image/jpeg"
                    },
                    "explination": "Non est tempora consequatur est et quia omnis.\nNumquam et voluptatem aliquam porro."
                },
                {
                    "id": "fWSH0QHhFo-",
                    "media": {
                        "id": "C1uFNzmBx23",
                        "url": "https://storage.googleapis.com/myapp-dev/dummy_profile_photos/profile_default_1.jpg",
                        "name": "test",
                        "mimeType": "image/jpeg"
                    },
                    "explination": "Et delectus sint illum culpa.\nVoluptate iure magnam maiores inventore quo eum amet sapiente.\nPossimus vitae possimus amet similique."
                },
                {
                    "id": "j6wG8I2_cok",
                    "media": {
                        "id": "tFJ2XEqXaji",
                        "url": "https://storage.googleapis.com/myapp-dev/dummy_profile_photos/profile_default_1.jpg",
                        "name": "test",
                        "mimeType": "image/jpeg"
                    },
                    "explination": "Earum facere quo minus maiores quis nobis et quo.\nVeniam facilis soluta alias fugiat et nobis asperiores in.\nEt et qui hic laborum eligendi temporibus et."
                }
            ],
            "trendingScore": 1,
            "ipAddressOfUser": "84.211.160.110",
            "recipeLangaugeKey": "en",
            "totalTimeToCookMs": 900000
        },
        "user": {
            "id": "ckEQtm",
            "bio": "",
            "name": "Olly",
            "isBanned": false,
            "location": {
                "city": "Oslo",
                "country": "NO",
                "isRough": true,
                "latLong": [
                    59.8468,
                    10.7747
                ],
                "longName": "NO",
                "timezone": "Europe/Oslo",
                "shortName": "Oslo"
            },
            "createdAt": "2022-10-25T12:27:18.364+02:00",
            "isDeleted": false,
            "isFlagged": false,
            "isVisable": true,
            "updatedAt": "2022-10-27T10:56:41.802+02:00",
            "profileUrl": {
                "id": "L9RtfueTEWU",
                "url": "https://storage.googleapis.com/myapp-dev/uploads/ckEQtm/images/QufsJIMvt_f9axN3E",
                "name": "me-nice-bg.jpg",
                "mimeType": "image/jpeg",
                "originalUrl": "https://storage.googleapis.com/myapp-dev/uploads/ckEQtm/images/QufsJIMvt_f9axN3E_original"
            },
            "phoneNumber": "+1234455",
            "recipeViews": 0,
            "profileViews": 0,
            "followerCount": 1,
            "trendingScore": 1,
            "followingCount": 1,
            "ipAddressOfUser": "176.111.206.130",
            "isPlatformAdmin": false,
            "isCompanyEnabled": false,
            "lastOpenedAppIso": "2022-10-27T10:56:41.801+02:00",
            "recipeTrendingScore": 1
        },
        "originalSharedPost": null
    }
]
postgresql

1条答案

按热度按时间
68bkxrlz

68bkxrlz1#

我想出来了
必须与递归（https://www.postgresql.org/docs/current/queries-with.html）一起使用

WITH RECURSIVE rpost AS (SELECT 
    posts.*,
    (
        SELECT to_jsonb(recipes)
        FROM myapp.recipes 
        WHERE recipes.id = posts."recipeId"
    ) as recipe,
    (
        SELECT to_jsonb(users)
        FROM myapp.users 
        WHERE users.id = posts."userId"
    ) as user
FROM myapp.posts)

# SECOND QUERY REFERENCES WITH

SELECT *,
    (
        SELECT to_jsonb(posts)
        FROM myapp.posts 
        WHERE posts.id = rpost."originalPostId"
    ) as "originalSharedPost"
FROM rpost

--
如果你想要与原始数据相同的数据类型，你可以在第二个查询中使用json builder，如下所示：

SELECT *,
    (

        SELECT json_build_object(
        'post',to_jsonb(posts), 
        'userLight',
            (
                SELECT to_jsonb(users)
                FROM myapp.users 
                WHERE users.id = posts."userId"
            ),
        'recipe',
            (
                SELECT to_jsonb(recipes)
                FROM myapp.recipes 
                WHERE recipes.id = posts."recipeId"
            )
        )
        FROM myapp.posts 
        WHERE posts.id = rpost."originalPostId"

    ) as "originalSharedPost"
FROM rpost
赞(0）分享  回复(0）举报  2022-11-04
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com