sql - 如何在一个查询中进行两次左连接？

Question

如何在一个查询中进行两次左连接或以其他方式在结果列表中获取数据，如下所示？

我想要 media 下的 media_information

{ 
  id: 9,
  gallery_id: 25,
  media_id: 9,
  media: { 
    id: 9,
    media_information: {
      id: 9,
      media_id: 9,
    }
  },
}

我试过的查询

var dbQuery = `SELECT
  gm.*,
  row_to_json(m.*) as media,
  row_to_json(mi.*) as media_information
  FROM "gallery_media" gm 
  LEFT JOIN media m ON gm.media_id = m.id
  LEFT JOIN media_information mi ON gm.media_id = mi.media_id
  WHERE "gallery_id" = $1 
  ORDER BY gm.create_date DESC OFFSET $2 LIMIT $3`;

结果

{ 
  id: 9,
  gallery_id: 25,
  media_id: 9,
  media: { 
    id: 9,
  },
  media_information: {
    id: 9,
    media_id: 9,
  }
}

更新

我也尝试更改 ON m.id = mi.media_id 但结果没有任何变化

var dbQuery = `SELECT
  gm.*,
  row_to_json(m.*) as media,
  row_to_json(mi.*) as media_information
  FROM "gallery_media" gm 
  LEFT JOIN media m ON gm.media_id = m.id
  LEFT JOIN media_information mi ON m.id = mi.media_id
  WHERE "gallery_id" = $1 
  ORDER BY gm.create_date DESC OFFSET $2 LIMIT $3`;

表格

CREATE TABLE IF NOT EXISTS "gallery_media"(
    "id" SERIAL NOT NULL,
    "gallery_id" integer NOT NULL,
    "media_id" integer NOT NULL,
    PRIMARY KEY ("id")
  );
CREATE TABLE IF NOT EXISTS "media"(
    "id" SERIAL NOT NULL,
    PRIMARY KEY ("id")
  );

CREATE TABLE IF NOT EXISTS "media_information"(
    "id" SERIAL NOT NULL,
    "media_id" integer NOT NULL,
    PRIMARY KEY ("id")  );

http://sqlfiddle.com/#!15/ff805

Answer 1

我认为这就是您所追求的:

var dbQuery = `SELECT                                    
  gm.*,                                                  
  row_to_json(m.*) as media,                             
  row_to_json(mi.*) as media_information                 
  FROM "gallery_media" gm                                
  LEFT JOIN media m ON gm.media_id = m.id                
  LEFT JOIN media_information mi ON m.id = mi.media_id   
  WHERE "gallery_id" = $1                                
  ORDER BY gm.create_date DESC OFFSET $2 LIMIT $3`;     

即:在 media 而不是 gallery_media 上进行第二次 LEFT JOIN