diff options
Diffstat (limited to 'db/migrations/0180_amusing_post.sql')
| -rw-r--r-- | db/migrations/0180_amusing_post.sql | 163 |
1 files changed, 163 insertions, 0 deletions
diff --git a/db/migrations/0180_amusing_post.sql b/db/migrations/0180_amusing_post.sql new file mode 100644 index 00000000..9ffa4e68 --- /dev/null +++ b/db/migrations/0180_amusing_post.sql @@ -0,0 +1,163 @@ +DROP VIEW "public"."qna_answer_view";--> statement-breakpoint +DROP VIEW "public"."qna_comment_view";--> statement-breakpoint +DROP VIEW "public"."qna_view";--> statement-breakpoint +CREATE VIEW "public"."qna_answer_view" AS (select "qna_answer"."id", "qna_answer"."qna_id", "qna_answer"."content", "qna_answer"."author", "qna_answer"."created_at" as "created_at", "qna_answer"."updated_at" as "updated_at", "qna_answer"."is_deleted" as "is_deleted", "qna_answer"."deleted_at" as "deleted_at", "qna"."title" as "question_title", "qna"."category" as "question_category", "qna"."author" as "question_author", "qna"."created_at" as "question_created_at", "users"."name" as "author_name", "users"."email" as "author_email", "users"."domain" as "author_domain", "users"."phone" as "author_phone", "users"."image_url" as "author_image_url", "users"."language" as "author_language", "vendors"."vendor_name" as "vendor_name", "vendors"."vendor_code" as "vendor_code", "tech_vendors"."vendor_name" as "tech_vendor_name", "tech_vendors"."vendor_code" as "tech_vendor_code", COALESCE("vendors"."vendor_name", "tech_vendors"."vendor_name") as "company_name", COALESCE("vendors"."vendor_code", "tech_vendors"."vendor_code") as "company_code", + CASE + WHEN "vendors"."vendor_name" IS NOT NULL THEN 'vendor' + WHEN "tech_vendors"."vendor_name" IS NOT NULL THEN 'techVendor' + ELSE NULL + END + as "vendor_type", ( + SELECT COUNT(*)::int + FROM "qna_comments" qc + WHERE qc.answer_id = "qna_answer"."id" + AND qc.is_deleted = false + ) as "total_comments", ( + SELECT COUNT(*)::int + FROM "qna_comments" qc + WHERE qc.answer_id = "qna_answer"."id" + AND qc.is_deleted = false + ) as "comment_count", ( + SELECT COUNT(*)::int + FROM "qna_comments" qc + WHERE qc.answer_id = "qna_answer"."id" + AND qc.parent_comment_id IS NULL + AND qc.is_deleted = false + ) as "parent_comments_count", ( + SELECT COUNT(*)::int + FROM "qna_comments" qc + WHERE qc.answer_id = "qna_answer"."id" + AND qc.parent_comment_id IS NOT NULL + AND qc.is_deleted = false + ) as "child_comments_count", ( + SELECT MAX(qc.created_at) + FROM "qna_comments" qc + WHERE qc.answer_id = "qna_answer"."id" + AND qc.is_deleted = false + ) as "last_commented_at", ( + SELECT ROW_NUMBER() OVER ( + PARTITION BY qa2.qna_id + ORDER BY qa2.created_at ASC + ) + FROM "qna_answer" qa2 + WHERE qa2.id = "qna_answer"."id" + AND qa2.is_deleted = false + ) as "answer_order", ( + "qna_answer"."id" = ( + SELECT qa2.id + FROM "qna_answer" qa2 + WHERE qa2.qna_id = "qna_answer"."qna_id" + AND qa2.is_deleted = false + ORDER BY qa2.created_at ASC + LIMIT 1 + ) + ) as "is_first_answer", ( + "qna_answer"."id" = ( + SELECT qa2.id + FROM "qna_answer" qa2 + WHERE qa2.qna_id = "qna_answer"."qna_id" + AND qa2.is_deleted = false + ORDER BY qa2.created_at DESC + LIMIT 1 + ) + ) as "is_latest_answer" from "qna_answer" left join "qna" on "qna_answer"."qna_id" = "qna"."id" left join "users" on "qna_answer"."author" = "users"."id" left join "vendors" on "users"."company_id" = "vendors"."id" left join "tech_vendors" on "users"."tech_company_id" = "tech_vendors"."id" where "qna_answer"."is_deleted" = false order by "qna_answer"."created_at");--> statement-breakpoint + +CREATE VIEW "public"."qna_comment_view" AS (select "qna_comments"."id", "qna_comments"."content", "qna_comments"."author", "qna_comments"."answer_id", "qna_comments"."parent_comment_id", "qna_comments"."created_at" as "created_at", "qna_comments"."updated_at" as "updated_at", "qna_comments"."is_deleted" as "is_deleted", "qna_comments"."deleted_at" as "deleted_at", "qna_answer"."content" as "answer_content", "qna_answer"."author" as "answer_author", "qna_answer"."created_at" as "answer_created_at", "qna_answer"."qna_id" as "qna_id", "qna"."title" as "question_title", "qna"."category" as "question_category", "qna"."author" as "question_author", "users"."name" as "author_name", "users"."email" as "author_email", "users"."domain" as "author_domain", "users"."image_url" as "author_image_url", "vendors"."vendor_name" as "vendor_name", "tech_vendors"."vendor_name" as "tech_vendor_name", COALESCE("vendors"."vendor_name", "tech_vendors"."vendor_name") as "company_name", + CASE + WHEN "vendors"."vendor_name" IS NOT NULL THEN 'vendor' + WHEN "tech_vendors"."vendor_name" IS NOT NULL THEN 'techVendor' + ELSE NULL + END + as "vendor_type", "qna_comments"."parent_comment_id" IS NULL as "is_parent_comment", "qna_comments"."parent_comment_id" IS NOT NULL as "is_child_comment", ( + SELECT COUNT(*)::int + FROM "qna_comments" qc2 + WHERE qc2.parent_comment_id = "qna_comments"."id" + AND qc2.is_deleted = false + ) as "child_comments_count", ( + SELECT COUNT(*) > 0 + FROM "qna_comments" qc2 + WHERE qc2.parent_comment_id = "qna_comments"."id" + AND qc2.is_deleted = false + ) as "has_child_comments", + CASE + WHEN "qna_comments"."parent_comment_id" IS NULL THEN 0 + ELSE 1 + END + as "comment_depth", ( + SELECT ROW_NUMBER() OVER ( + PARTITION BY qc2.answer_id, qc2.parent_comment_id + ORDER BY qc2.created_at ASC + ) + FROM "qna_comments" qc2 + WHERE qc2.id = "qna_comments"."id" + AND qc2.is_deleted = false + ) as "comment_order" from "qna_comments" left join "qna_answer" on "qna_comments"."answer_id" = "qna_answer"."id" left join "qna" on "qna_answer"."qna_id" = "qna"."id" left join "users" on "qna_comments"."author" = "users"."id" left join "vendors" on "users"."company_id" = "vendors"."id" left join "tech_vendors" on "users"."tech_company_id" = "tech_vendors"."id" where "qna_comments"."is_deleted" = false order by "qna_comments"."created_at");--> statement-breakpoint + +CREATE VIEW "public"."qna_view" AS (select "qna"."id", "qna"."title", "qna"."content", "qna"."author", "qna"."category", "qna"."created_at", "qna"."updated_at", "qna"."is_deleted", "qna"."deleted_at", "users"."name", "users"."email", "users"."domain", "users"."phone", "users"."image_url", "users"."language", "users"."is_active", "users"."last_login_at", "vendors"."vendor_name" as "vendor_name", "vendors"."vendor_code" as "vendor_code", "vendors"."status" as "vendor_status", "vendors"."country" as "vendor_country", "vendors"."business_size" as "vendor_business_size", "tech_vendors"."vendor_name" as "tech_vendor_name", "tech_vendors"."vendor_code" as "tech_vendor_code", "tech_vendors"."status" as "tech_vendor_status", "tech_vendors"."country" as "tech_vendor_country", "tech_vendors"."tech_vendor_type" as "tech_vendor_type", COALESCE("vendors"."vendor_name", "tech_vendors"."vendor_name") as "company_name", COALESCE("vendors"."vendor_code", "tech_vendors"."vendor_code") as "company_code", COALESCE("vendors"."country", "tech_vendors"."country") as "company_country", + CASE + WHEN "vendors"."vendor_name" IS NOT NULL THEN 'vendor' + WHEN "tech_vendors"."vendor_name" IS NOT NULL THEN 'techVendor' + ELSE NULL + END + as "vendor_type", ( + SELECT COUNT(*)::int + FROM "qna_answer" qa + WHERE qa.qna_id = "qna"."id" + AND qa.is_deleted = false + ) as "total_answers", ( + SELECT COUNT(*)::int + FROM "qna_answer" qa + WHERE qa.qna_id = "qna"."id" + AND qa.is_deleted = false + ) as "answer_count", ( + SELECT MAX(qa.created_at) + FROM "qna_answer" qa + WHERE qa.qna_id = "qna"."id" + AND qa.is_deleted = false + ) as "last_answered_at", ( + SELECT MIN(qa.created_at) + FROM "qna_answer" qa + WHERE qa.qna_id = "qna"."id" + AND qa.is_deleted = false + ) as "first_answered_at", ( + SELECT COUNT(*)::int + FROM "qna_comments" qc + INNER JOIN "qna_answer" qa ON qc.answer_id = qa.id + WHERE qa.qna_id = "qna"."id" + AND qc.is_deleted = false + AND qa.is_deleted = false + ) as "total_comments", ( + SELECT GREATEST( + "qna"."updated_at", + COALESCE(( + SELECT MAX(qa.updated_at) + FROM "qna_answer" qa + WHERE qa.qna_id = "qna"."id" + AND qa.is_deleted = false + ), "qna"."updated_at"), + COALESCE(( + SELECT MAX(qc.updated_at) + FROM "qna_comments" qc + INNER JOIN "qna_answer" qa ON qc.answer_id = qa.id + WHERE qa.qna_id = "qna"."id" + AND qc.is_deleted = false + AND qa.is_deleted = false + ), "qna"."updated_at") + ) + ) as "last_activity_at", ( + SELECT COUNT(*) > 0 + FROM "qna_answer" qa + WHERE qa.qna_id = "qna"."id" + AND qa.is_deleted = false + ) as "has_answers", ( + SELECT COUNT(*) > 0 + FROM "qna_answer" qa + WHERE qa.qna_id = "qna"."id" + AND qa.is_deleted = false + ) as "is_answered", ( + (SELECT COUNT(*) FROM "qna_answer" qa WHERE qa.qna_id = "qna"."id" AND qa.is_deleted = false) >= 3 + OR + (SELECT COUNT(*) FROM "qna_comments" qc + INNER JOIN "qna_answer" qa ON qc.answer_id = qa.id + WHERE qa.qna_id = "qna"."id" AND qc.is_deleted = false AND qa.is_deleted = false) >= 5 + ) as "is_popular" from "qna" left join "users" on "qna"."author" = "users"."id" left join "vendors" on "users"."company_id" = "vendors"."id" left join "tech_vendors" on "users"."tech_company_id" = "tech_vendors"."id" where "qna"."is_deleted" = false order by "qna"."created_at");
\ No newline at end of file |