mysql - 将三个独立的模型连接成一个结果

Question

我有一个模型 Newsfeed，它包含三个子项:NewsfeedImage、NewsfeedText 和 NewsfeedLink。

新闻源

新闻源图片

新闻源链接

新闻提要文本

正如您从三个子模型中看到的，每个模型都代表不同类型的新闻源对象。我的问题是，如何从 Newsfeed 中 select * 并包含每个附属子模型？所需的输出(忽略 JSON):

[{
    "id": "1",
    "userId": "1",
    "commentCount": "0",
    "likeCount": "0",
    "updatedAt": "2015-12-13 12:12:32",
    "createdAt": "2015-12-13 12:12:32",
    "newsfeedId": "1",
    "model": "1",
    "imgUrl": "http://i.imgur.com/...",
    "title": "Has anyone..."
}, {
    "id": "1",
    "userId": "1",
    "commentCount": "0",
    "likeCount": "0",
    "updatedAt": "2015-12-13 12:12:32",
    "createdAt": "2015-12-13 12:12:32",
    "newsfeedId": "2",
    "model": "2",
    "linkShort": "http://goo.gl/...",
    "linkLong": "http://www....",
    "title": "Has anyone ...?"
}, {
    "id": "1",
    "userId": "1",
    "commentCount": "0",
    "likeCount": "0",
    "updatedAt": "2015-12-13 12:12:32",
    "createdAt": "2015-12-13 12:12:32",
    "newsfeedId": "3",
    "model": "3",
    "body": "How much ...?",
    "title": "Question ..."
}]

我最初尝试使用联接，但数据相交不正确。感谢您的帮助，并感谢您花时间查看我的问题。

Answer 1

SQL查询:

SELECT * FROM Newsfeed
LEFT JOIN (
    SELECT newsfeedId, model, imgUrl, 
        null AS linkShort, null AS linkLong, null AS body, 
        title
    FROM NewsfeedImage 
    WHERE NewsfeedImage.newsfeedid = Newsfeed.id

    UNION ALL 

    SELECT newsfeedId, model, null AS imgUrl, 
        linkShort, linkLong, null AS body, 
        title
    FROM NewsfeedLink 
    WHERE NewsfeedLink.newsfeedid = Newsfeed.id

    UNION ALL

    SELECT newsfeedId, model, null imgUrl, 
        null AS linkShort, null AS linkLong, body, 
        title
    FROM NewsfeedText 
    WHERE NewsfeedText.newsfeedid = Newsfeed.id

) t

并删除程序脚本中带有 null 的字段。