Skip to content

Instantly share code, notes, and snippets.

@snakers4
Created April 26, 2017 14:55
Show Gist options
  • Save snakers4/f82c7eb0299f1de816b71ce231bd80e0 to your computer and use it in GitHub Desktop.
Save snakers4/f82c7eb0299f1de816b71ce231bd80e0 to your computer and use it in GitHub Desktop.
SELECT
TO_JSON(ARRAY_AGG(a))
FROM (
SELECT
raw_data.*,
'http://spark-in.me/post/'||raw_data.slug as disqus_article_url,
(SELECT to_json(array_agg(e)) FROM (
SELECT DISTINCT
at.\"id\" as tag_id,
at.title as tag_title,
at.alias as tag_alias
FROM
article
JOIN article_publication ap ON ap.article_id = raw_data.article_id AND article_id = raw_data.article_id
JOIN article_tags at ON at.\"id\" = ap.tag_id
) e) as article_tags,
(SELECT to_json(array_agg(e)) FROM (
SELECT
author.\"id\" as author_id,
author.alias as author_alias,
author.contact_json as author_contacts,
author.description as author_description,
author.header_picture as main_picture
FROM
author
WHERE
author.\"id\" = raw_data.author_id
) e) as author_info,
array_to_json(
array[
json_build_object (
'type',
'name',
'key',
'title',
'content',
raw_data.title
),
json_build_object (
'type',
'rel',
'key',
'canonical',
'content',
'http://spark-in.me/post/'||raw_data.slug
),
json_build_object (
'type',
'name',
'key',
'description',
'content',
raw_data.description
),
json_build_object (
'type',
'property',
'key',
'og:title',
'content',
raw_data.title
),
json_build_object (
'type',
'property',
'key',
'og:url',
'content',
'http://spark-in.me/post/'||raw_data.slug
),
json_build_object (
'type',
'property',
'key',
'og:image',
'content',
raw_data.main_picture
),
json_build_object (
'type',
'property',
'key',
'og:description',
'content',
raw_data.description
),
json_build_object (
'type',
'property',
'key',
'og:site_name',
'content',
'Spark in me'
),
json_build_object (
'type',
'rel',
'key',
'author',
'content',
'http://spark-in.me/author/'||raw_data.author_alias
),
json_build_object (
'type',
'property',
'key',
'article:author',
'content',
'http://spark-in.me/author/'||raw_data.author_alias
),
json_build_object (
'type',
'property',
'key',
'author',
'content',
raw_data.author_title
),
json_build_object (
'type',
'property',
'key',
'article:published_time',
'content',
raw_data.created
)
]
) as article_meta,
(SELECT to_json(h) FROM (
SELECT
'http://schema.org' as \"@context\",
'Article' as \"@type\",
raw_data.published as \"datePublished\",
raw_data.modified as \"dateModified\",
raw_data.title as headline,
raw_data.title as \"name\",
(SELECT to_json(k) FROM (
SELECT
'ImageObject' as \"@type\",
raw_data.main_picture as url
) k) as image,
(SELECT to_json(k) FROM (
SELECT
'Person' as \"@type\",
raw_data.author_title as \"name\",
'http://spark-in.me/author/'||raw_data.author_alias as url
) k) as author,
(SELECT array_to_json(j.agg) FROM (
SELECT
\"array_agg\"(temp1.title) as agg
FROM
(
SELECT DISTINCT
at.title
FROM
article
JOIN article_publication ap ON ap.article_id = raw_data.article_id AND article_id = raw_data.article_id
JOIN article_tags at ON at.\"id\" = ap.tag_id
) temp1
) j) as keywords
) h) as ld_json
FROM
(
SELECT DISTINCT
'published' as status,
'post' as type_slug,
ar.\"id\" as article_id,
ar.author_id as author_id,
ar.creation_date as created,
ar.main_picture as main_picture,
ar.feed_picture as feed_picture,
ar.title as title,
ar.subtitle as subtitle,
ar.\"alias\" as \"slug\",
ap.time as published,
ar.last_modified_date as modified,
ar.description as description,
\"a\".\"alias\" as author_alias,
\"a\".title as author_title
".$fullClause."
FROM
article ar
JOIN article_publication ap ON ap.article_id = ar.\"id\" AND ap.is_actual = 't' AND ar.is_actual = 't' AND ap.target_id = 2
JOIN author a ON ar.author_id = a.\"id\"
ORDER BY
ap.time DESC,ar.id DESC
) raw_data
) a
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment