postgres_query.sql

COPY (
SELECT
  A2.id AS "記事ID",
  A2.title AS "タイトル",
  A2.sub_title AS "サブタイトル",
  CONCAT('https://XXX.com/', IMG.width, '/', IMG.height, '/', IMG.filename) AS "画像URL",
  A2.content AS "本文",
  A2.html_content AS "HTML本文",
  A2.meta_description AS "META ディスクリプション",
  A2.author_id AS "投稿者ID",
  AUTHOR.display_name AS "投稿者",
  A2.editor_id AS "編集担当者ID",
  EDITOR.display_name AS "編集担当者",
  CAT.name AS "カテゴリ",
  A2.status AS "Status(1:非公開, 2:公開)",
  A2.published_at AS "公開日",
  A2.trash_flg AS "コミ箱フラグ",
  A2.pr_label_flg AS "PRラベルを表示する",
  A2.top_random_flg AS "TOPランダム表示枠に入れる",
  A2.nolist_flg AS "TOPの一覧で表示しない",
  A2.noindex_flg AS "検索結果に表示させない",
  A2.nofollow_flg AS "クローラにページ内リンクを回遊させない",
  A2.twitter_card AS "Twitterカードタイプ(1:要約と画像, 2:画像)",
  AFR.feature_id AS "特集ID",
  F.title AS "特集タイトル",
  s.ra_ids AS "関連記事ID",
  s.ra_titles AS "関連記事タイトル"
FROM (
  SELECT
    A.id AS id,
    array_to_string(array_agg(RR.child_id), E'\n') AS ra_ids,
    array_to_string(array_agg(RR.title), E'\n') AS ra_titles
  FROM
    articles AS A
  LEFT OUTER JOIN
    related_articles AS RR
  ON
    A.id = RR.parent_id
  WHERE
    A.id IS NOT NULL
  GROUP BY
    A.id
) s
  JOIN
    articles A2
  ON
    s.id = A2.id
  JOIN
    operators AS AUTHOR ON A2.author_id = AUTHOR.id
  JOIN
    operators AS EDITOR ON A2.editor_id = EDITOR.id
  JOIN
    categories AS CAT ON A2.category_id = CAT.id
  JOIN
    image_files AS IMG ON A2.image_id = IMG.image_id
  LEFT OUTER JOIN
    article_feature_relations AS AFR ON A2.id = AFR.article_id LEFT OUTER JOIN features AS F ON AFR.feature_id = F.id
  WHERE
    A2.category_id IN (
      SELECT descendant_id FROM category_relations WHERE ancestor_id = 5
    )
    OR A2.category_id = 5
  ORDER BY A2.id DESC
  -- LIMIT 100
) TO '/var/lib/postgresql/data/1.csv' DELIMITER ',' CSV HEADER;