Recorded for posterity

Blog post sharing hell

scope :for_region, lambda { |region| 
    joins("LEFT OUTER JOIN blog_post_shared_regions ON blog_post_shared_regions.blog_post_id = blog_posts.id")
      .where("blog_posts.region_id = ? OR (blog_post_shared_regions.region_id = ? AND blog_post_shared_regions.visible = true)", region.id, region.id)
      .order( "CASE WHEN blog_post_shared_regions.shared_at IS NULL THEN blog_posts.updated_at WHEN blog_post_shared_regions.shared_at > blog_posts.updated_at THEN blog_post_shared_regions.shared_at ELSE blog_posts.updated_at END DESC") 
}