Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT "stories"."id" AS "Id",
- "stories"."title" AS "Title",
- "stories"."publication_id" AS "Publication Id",
- "publications"."name" AS "Publication Name",
- "story_authors"."authors" AS "Authors",
- "stories"."date" AS "Date",
- "stories"."media_type" AS "Media Type",
- "stories"."url" AS "URL",
- "stories"."length" AS "Length",
- "stories"."solutions_3" AS "Solutions 3",
- "stories"."catalyzed_by_sjn" AS "Catalyzed by SJN",
- "stories"."latitude" AS "Latitude",
- "stories"."longitude" AS "Longitude",
- "stories"."city" AS "City",
- "stories"."state" AS "State",
- "stories"."country" AS "Country",
- "stories"."story_scope" AS "Story Scope",
- "stories"."research" AS "Research",
- "stories"."hashed_id" AS "Hashed Id",
- "stories"."favorite_count" AS "Favorite Count",
- "stories"."share_count" AS "Share Count",
- "stories"."wow_high_cal" AS "WOW (High Caliber)",
- "stories"."wow_impact" AS "WOW (Impactful)",
- "stories"."wow_storytelling" AS "WOW (Innovative Storytelling)",
- "users"."email" AS "Created by User",
- "stories"."description" AS "Description Id",
- "stories"."audio_description" AS "Audio Description",
- "critical_success_factors"."terms" AS "Success Factors",
- "issue_areas"."terms" AS "Issue Areas"
- FROM "stories"
- LEFT JOIN "users"
- ON ( "stories"."admin_user_id" = "users"."id" )
- LEFT JOIN "publications"
- ON ( "stories"."publication_id" = "publications"."id" )
- LEFT JOIN (SELECT "story_authors"."story_id",
- array_to_string(array_agg("authors"."name"), ' | ') AS "authors"
- FROM "story_authors"
- INNER JOIN "authors"
- ON ( "story_authors"."author_id" = "authors"."id" )
- GROUP BY "story_authors"."story_id") AS "story_authors"
- ON ( "stories"."id" = "story_authors"."story_id" )
- LEFT JOIN (SELECT "story_critical_success_factors"."story_id",
- array_to_string(array_agg("critical_success_factors"."term"), ', ') AS "terms"
- FROM "critical_success_factors"
- INNER JOIN "story_critical_success_factors"
- ON ( "story_critical_success_factors"."critical_success_factor_id" = "critical_success_factors"."id" )
- GROUP BY "story_critical_success_factors"."story_id") AS "critical_success_factors"
- ON ( "critical_success_factors"."story_id" = "stories"."id" )
- LEFT JOIN (SELECT "story_issue_areas"."story_id",
- array_to_string(array_agg("issue_areas"."term"), ', ') AS "terms"
- FROM "issue_areas"
- INNER JOIN "story_issue_areas"
- ON ( "story_issue_areas"."issue_area_id" = "issue_areas"."id" )
- GROUP BY "story_issue_areas"."story_id") AS "issue_areas"
- ON ( "issue_areas"."story_id" = "stories"."id" )
- ORDER BY lower("stories"."title") ASC;
Add Comment
Please, Sign In to add comment