时间戳

首页 > 解决方案 > 联合所有并保持活跃的记录关系？

sql - 联合所有并保持活跃的记录关系？

问题描述

这是我的查询

SELECT * FROM (SELECT id as broadcast_id, null as message_id, company_id, title, category, publish_at as created_at, image_file_name, null as is_featured, summary, video_link, file FROM broadcasts
UNION ALL
SELECT null, id, company_id, title, 'user_video', created_at, cover_photo_file_name, is_featured, content, video_link, null  FROM `messages` WHERE video_link is not null  and video_link != '')my_table
ORDER BY created_at DESC LIMIT 15

我想将所有内容保持为广播活动记录关系。

我实际上是使用活动记录扩展 gem 来完成这项工作的

    legacy_broadcasts = Broadcast.select(:id, "null as message_id", :publish_at, :company_id, :title, :category, :image_file_name, :summary, "null as is_featured", :file, :video_link, :url)
    user_videos       = Message.select("null", :id, :created_at, :company_id, :title, "\"user_video\" as category", :cover_photo_file_name, :content, :is_featured, "null as file", :video_link, "null as url").where('video_link is not null  and video_link != \'\'')
    base              = Broadcast.union_all(legacy_broadcasts, user_videos)
                                 .includes(:company)
                                 .published
                                 .order(publish_at: :desc)

    @q                = base.ransack(params[:q])
    @video_broadcasts = base.has_video_link
                            .paginate(
                              page:     page,
                              per_page: 5)
                            .to_a

    @broadcasts = @q
                    .result
                    .paginate(
                      page:     page,
                      per_page: 9)
                    .to_a

但不幸的是，我不允许将 pg gem 添加到活动记录扩展 gem 所需的 prod 中。关于如何最好地做到这一点的任何想法？

标签： sqlruby-on-railsrubyactiverecord

解决方案

我所做的是使用 gem 并复制它创建的查询。从那里我做了以下

    legacy_broadcasts = Broadcast.select(:id, "null as message_id", :publish_at, :company_id, :title, :category, :image_file_name, :summary, "null as is_featured", :file, :video_link, :url).includes(:company).to_sql
    user_videos       = Message.select("null", :id, :created_at, :company_id, :title, "\"user_video\" as category", :cover_photo_file_name, :content, :is_featured, "null as file", :video_link, "null as url").where('video_link is not null  and video_link != \'\'').includes(:company).to_sql
    base              = Broadcast.from("(((#{legacy_broadcasts} ) UNION ( #{user_videos} ))) broadcasts")
                                 .includes(:company)
                                 .published
                                 .order(publish_at: :desc)

推荐阅读