Oracle SQL连接/合并2个表并按ID分组/排序，以创建类似Facebook的时间线

Question

对于一项课程作业，我必须创建一个类似Facebook的应用程序。

目前，我正在"时间线"页面上工作，并努力解决所需的SQL查询的复杂性。

我想要的结果是在单个表格中显示帖子和回复，并对或进行分组或排序，以便回复位于相应帖子下方

eg post_id        text
     1          First post
     1          Response 1 to post 1
     1          Response 2 to post 1
     2          Second post
     2          Response 1 to post 2
     2          Response 2 to post 2

数据库架构如下：

create or replace type post_obj as object (
   post_id number,
   post_text varchar2(500),
   user_posted_by ref user_obj,
   post_date timestamp,
   map member function get_postid return number) not final;
create table post_obj_tbl of post_obj;

create or replace type response_obj as object (
   response_id number,
   response_text varchar2(500),
   post_details ref post_obj,
   user_posted_by ref user_obj,
   response_date timestamp,
   map member function get_responseid return number) not final;
create table response_obj_tbl of response_obj;

到目前为止，我已经设法让两个表连接起来，以便我得到一个显示帖子和响应的表格，但它会自动按用户 ID 分组。下面是查询：

SELECT p_tbl.user_posted_by.username, p_tbl.post_text, p_tbl.post_date
FROM post_obj_tbl p_tbl
WHERE 
p_tbl.user_posted_by.user_id = (
   SELECT u_tbl.user_id
   FROM user_obj_tbl u_tbl 
   WHERE u_tbl.username = (select V('APP_USER') from DUAL) )
OR
p_tbl.user_posted_by.user_id in (
   SELECT f_tbl.friend_id 
   FROM friends_obj_tbl f_tbl
   WHERE f_tbl.user_id = (
      SELECT u_tbl.user_id
      FROM user_obj_tbl u_tbl 
      WHERE u_tbl.username = (select V('APP_USER') from DUAL) )
   )
UNION
SELECT r_tbl.user_posted_by.username, r_tbl.response_text, r_tbl.response_date
FROM response_obj_tbl r_tbl, post_obj_tbl p_tbl
WHERE r_tbl.post_details.post_id = p_tbl.post_id;

我真的希望这是有道理的...弄乱了我的脑袋！任何帮助非常感谢:)

Answer 1

首先，为什么要创建对象表？通常的方法是创建这样的表：

create table post_obj  (
   post_id number,
   post_text varchar2(500),
   user_posted_by number,
   post_date timestamp);

使用这样的表要容易得多，尤其是在 APEX 中。
关于查询。我将采取一部分：

SELECT p_tbl.user_posted_by.username, p_tbl.post_text, p_tbl.post_date
FROM post_obj_tbl p_tbl
WHERE 
p_tbl.user_posted_by.user_id = (
   SELECT u_tbl.user_id
   FROM user_obj_tbl u_tbl 
   WHERE u_tbl.username = (select V('APP_USER') from DUAL) )

它可以转换为：

SELECT p_tbl.user_posted_by.username, p_tbl.post_text, p_tbl.post_date
FROM   post_obj_tbl p_tbl,
       user_obj_tbl u_tbl
WHERE  p_tbl.user_posted_by = u_tbl.user_id
   AND u_tbl.username = V('APP_USER')