Ruby-与多个子查询查询

Question

如何在以下SQL语句中使用Ruby编写条件查询？我相信我们需要在service> service_tempaltes模型上定义alters_to，然后才能查询其他表格？

select * from service_templates where miq_group_id IN ( SELECT miq_group_id from entitlements where miq_user_role_id IN (SELECT id from miq_user_roles where settings is NULL) );

service_templates默认情况下，除非我们定义alters_to，否则我不确定是否有必要。但是我想根据上述查询在“范围”部分中编写一个条件。

scope :public_service_templates,                  ->         { where(miq_user_roles: { settings: nil }) }

表格架构：

Schema of `miq_groups table:

  create_table "miq_groups", force: :cascade do |t|
    t.string "description"
    t.string "group_type"
    t.integer "sequence"
    t.datetime "created_on"
    t.datetime "updated_on"
    t.text "settings"
    t.bigint "tenant_id"
    t.string "detailed_description"
  end

Schema of `entitlements table:

  create_table "entitlements", force: :cascade do |t|
    t.bigint "miq_group_id"
    t.bigint "miq_user_role_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.text "filters"
    t.text "filter_expression"
    t.index ["miq_group_id"], name: "index_entitlements_on_miq_group_id"
  end


Schema of miq_uer_roles table:

  create_table "miq_user_roles", force: :cascade do |t|
    t.string "name"
    t.boolean "read_only"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.text "settings"
  end

Schema of service_templates table:

  create_table "service_templates", force: :cascade do |t|
    t.string "name"
    t.string "description"
    t.string "guid"
    t.string "type"
    t.bigint "service_template_id"
    t.text "options"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.boolean "display"
    t.bigint "evm_owner_id"
    t.bigint "miq_group_id"
    t.string "service_type"
    t.string "prov_type"
    t.float "provision_cost"
    t.bigint "service_template_catalog_id"
    t.text "long_description"
    t.bigint "tenant_id"
    t.string "generic_subtype"
    t.datetime "deleted_on"
    t.boolean "internal"
    t.bigint "zone_id"
    t.bigint "currency_id"
    t.float "price"
    t.index ["currency_id"], name: "index_service_templates_on_currency_id"
    t.index ["generic_subtype"], name: "index_service_templates_on_generic_subtype"
    t.index ["type"], name: "index_service_templates_on_type"
  end

service_templates的模型定义。

class ServiceTemplate < ApplicationRecord
  include SupportsFeatureMixin

  DEFAULT_PROCESS_DELAY_BETWEEN_GROUPS = 120

  GENERIC_ITEM_SUBTYPES = {
    "custom"          => N_("Custom"),
    "vm"              => N_("Virtual Machine"),
    "playbook"        => N_("Playbook"),
    "hosted_database" => N_("Hosted Database"),
    "load_balancer"   => N_("Load Balancer"),
    "storage"         => N_("Storage")
  }.freeze

  SERVICE_TYPE_ATOMIC    = 'atomic'.freeze
  SERVICE_TYPE_COMPOSITE = 'composite'.freeze

  RESOURCE_ACTION_UPDATE_ATTRS = [:dialog,
                                  :dialog_id,
                                  :fqname,
                                  :configuration_template,
                                  :configuration_template_id,
                                  :configuration_template_type].freeze

  include CustomActionsMixin
  include ServiceMixin
  include OwnershipMixin
  include NewWithTypeStiMixin
  include TenancyMixin
  include ArchivedMixin
  include CiFeatureMixin
  include_concern 'Filter'
  include_concern 'Copy'


  validates :name, :presence => true
  belongs_to :tenant

  has_many   :service_templates, :through => :service_resources, :source => :resource, :source_type => 'ServiceTemplate'
  has_many   :services


  has_many :service_template_tenants, :dependent => :destroy
  has_many :additional_tenants, :through => :service_template_tenants, :source => :tenant, :dependent => :destroy

  has_one :picture, :dependent => :destroy, :as => :resource, :autosave => true

  belongs_to :service_template_catalog
  belongs_to :zone
  belongs_to :currency, :inverse_of => false

  has_many   :dialogs, -> { distinct }, :through => :resource_actions
  has_many   :miq_schedules, :as => :resource, :dependent => :destroy

  has_many   :miq_requests, :as => :source, :dependent => :nullify
  has_many   :active_requests, -> { where(:request_state => MiqRequest::ACTIVE_STATES) }, :as => :source, :class_name => "MiqRequest"
  ...
end