How can I write a where condition query using ruby for the following SQL statement? I believe we need to define a belongs_to on the service_tempaltes model before we can query other tables?
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 by default cannot join with other tables unless we define a belongs_to which I'm not sure if necessary. But I want to write a where condition in the scope section based on the above query.
scope :public_service_templates, -> { where(miq_user_roles: { settings: nil }) }
Model definition of entitlements:
class Entitlement < ApplicationRecord
belongs_to :miq_group
belongs_to :miq_user_role
...
end
Model definition of miq_user_roles.
class MiqUserRole < ApplicationRecord
DEFAULT_TENANT_ROLE_NAME = "EvmRole-tenant_administrator"
has_many :entitlements, :dependent => :restrict_with_exception
has_many :miq_groups, :through => :entitlements
has_and_belongs_to_many :miq_product_features, :join_table => :miq_roles_features
...
end
Model definition of miq_groups:
class MiqGroup < ApplicationRecord
USER_GROUP = "user"
SYSTEM_GROUP = "system"
TENANT_GROUP = "tenant"
belongs_to :tenant
has_one :entitlement, :dependent => :destroy, :autosave => true
has_one :miq_user_role, :through => :entitlement
has_and_belongs_to_many :users
has_many :vms, :dependent => :nullify
has_many :miq_templates, :dependent => :nullify
has_many :miq_reports, :dependent => :nullify
has_many :miq_report_results, :dependent => :nullify
has_many :miq_widget_contents, :dependent => :destroy
has_many :miq_widget_sets, :as => :owner, :dependent => :destroy
has_many :miq_product_features, :through => :miq_user_role
has_many :authentications, :dependent => :nullify
...
end
Model definition of 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"
virtual_column :type_display, :type => :string
virtual_column :template_valid, :type => :boolean
virtual_column :template_valid_error_message, :type => :string
virtual_column :archived, :type => :boolean
virtual_column :active, :type => :boolean
default_value_for :internal, false
default_value_for :service_type, SERVICE_TYPE_ATOMIC
default_value_for(:generic_subtype) { |st| 'custom' if st.prov_type == 'generic' }
virtual_has_one :config_info, :class_name => "Hash"
scope :with_service_template_catalog_id, ->(cat_id) { where(:service_template_catalog_id => cat_id) }
scope :without_service_template_catalog_id, -> { where(:service_template_catalog_id => nil) }
scope :with_existent_service_template_catalog_id, -> { where.not(:service_template_catalog_id => nil) }
scope :displayed, -> { where(:display => true) }
scope :public_service_templates, -> { where(:display => true) }
supports :order do
unsupported_reason_add(:order, 'Service template does not belong to a service catalog') unless service_template_catalog
unsupported_reason_add(:order, 'Service template is not configured to be displayed') unless display
end
alias orderable? supports_order?
alias validate_order supports_order?
def self.with_tenant(tenant_id)
tenant = Tenant.find(tenant_id)
where(:tenant_id => tenant.ancestor_ids + [tenant_id])\
end
