如何从两个表中获取数据 [英] How can I get data from two tables

问题描述

我被告知要创建一个 GET/feed 端点，用户可以在其中查看所有文章或 gif，首先显示最近发布的文章或 gif.这是我的文章和gif表

I was told to create an endpoint to GET /feed where users can view all articles or gifs, showing the most recently posted articles or gifs first. Here is my article and gif table

CREATE TABLE article(
    article_id SERIAL PRIMARY KEY,
    title VARCHAR(300),
    article text ,
    user_id INTEGER REFERENCES user(id) ON DELETE CASCADE,
    created_on TIMESTAMP DEFAULT Now() 
  )

CREATE TABLE gif(
    gif_id SERIAL PRIMARY KEY,
    title text NOT NULL ,
    cloudinary_id VARCHAR (3000),
    url VARCHAR(3000) ,
    user_id INTEGER REFERENCES user(id) ON DELETE CASCADE,
    created_on TIMESTAMP DEFAULT Now()
      )

如何查询我的数据库以根据文章和 gif 表的创建时间显示它们.

How can I query my db to show both the article and gif table based on when they where created.

推荐答案

使用虚拟列来说明不同的结构并添加联合以加入它们:

Using dummy columns to account for the different structures and add a union to join them:

SELECT * FROM (
    (SELECT article_id, title, article , NULL as cloudinary_id, NULL as url, user_id, created_on, 'article' as table_name  FROM article)
    UNION ALL
    (SELECT gif_id, title, NULL as article, cloudinary_id , url,  user_id, created_on , 'gif' as table_name  FROM gif)
) results
ORDER BY created_on ASC

这篇关于如何从两个表中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！