A platform for community discussion. Free, open, simple.
# frozen_string_literal: true
module DiscourseDataExplorer
class QueryFinder
def self.find(id)
default_query = Queries.default[id.to_s]
return raise ActiveRecord::RecordNotFound unless default_query
query = Query.find_by(id: id) || Query.new
query.attributes = default_query
query.user_id = Discourse::SYSTEM_USER_ID.to_s
query
end
end
class Query < ActiveRecord::Base
self.table_name = "data_explorer_queries"
DEFAULT_TAG = "default"
CUSTOM_TAG = "custom"
RESERVED_TAGS = [DEFAULT_TAG, CUSTOM_TAG].freeze
has_many :query_groups
has_many :groups, through: :query_groups
has_many :query_tag_mappings,
class_name: "DiscourseDataExplorer::QueryTagMapping",
dependent: :delete_all
has_many :tags,
-> { order(:name) },
class_name: "DiscourseDataExplorer::QueryTag",
through: :query_tag_mappings,
source: :query_tag
has_one :dashboard_report,
-> { where(source: AdminDashboardReportProvider.source_name) },
class_name: "::AdminDashboardReport",
foreign_key: :identifier
belongs_to :user
validates :name, presence: true
scope :for_group,
->(group) do
where(hidden: false).joins(
"INNER JOIN data_explorer_query_groups
ON data_explorer_query_groups.query_id = data_explorer_queries.id
AND data_explorer_query_groups.group_id = #{group.id}",
)
end
scope :user_queries, -> { where(hidden: false).where("id > 0") }
scope :filter_by_tags,
->(tag_names) do
Array
.wrap(tag_names)
.reduce(all) do |scope, tag_name|
automatic_condition =
case tag_name
when DEFAULT_TAG
"data_explorer_queries.id < 0"
when CUSTOM_TAG
"data_explorer_queries.id > 0"
else
"data_explorer_queries.id IN (:default_query_ids)"
end
scope.where(
<<~SQL,
(#{automatic_condition} OR EXISTS (
SELECT 1
FROM data_explorer_query_tags query_tag_mappings
JOIN data_explorer_tags query_tags
ON query_tags.id = query_tag_mappings.query_tag_id
WHERE query_tag_mappings.query_id = data_explorer_queries.id
AND query_tags.name = :tag_name
))
SQL
tag_name:,
default_query_ids: default_query_ids_tagged(tag_name),
)
end
end
def self.is_default_query?(id)
id.to_i < 0
end
def self.automatic_tag_names(id)
if is_default_query?(id)
[DEFAULT_TAG, *Queries.tags[id.to_i]]
elsif id.to_i > 0
[CUSTOM_TAG]
else
[]
end
end
def self.default_query_ids_tagged(tag_name)
Queries.tags.filter_map { |id, names| id if names.include?(tag_name) }
end
def params
@params ||= Parameter.create_from_sql(sql)
end
def cast_params(input_params, opts = {})
result = {}.with_indifferent_access
params.each do |pobj|
result[pobj.identifier] = pobj.cast_to_ruby(input_params[pobj.identifier], opts)
end
result
end
def slug
Slug.for(name).presence || "query-#{id}"
end
def record_run!
persisted? ? update_columns(last_run_at: Time.now) : update!(last_run_at: Time.now)
DiscourseDataExplorer::QueryStat.log(id) unless Query.is_default_query?(id)
end
def self.find(id)
return super unless is_default_query?(id)
QueryFinder.find(id)
end
def self.available_tags
tag_names =
QueryTag
.joins(query_tag_mappings: :query)
.where(data_explorer_queries: { hidden: false })
.distinct
.pluck(:name)
(tag_names + RESERVED_TAGS + Queries.tags.values.flatten).uniq.sort_by(&:downcase)
end
def self.unpersisted_defaults(search: nil, tags: nil, tag: nil)
selected_tags = QueryTag.normalize_all(tags || tag)
persisted_ids = where("id < 0").pluck(:id).to_set
query_text = search&.downcase
Queries.default.filter_map do |_, attributes|
next if persisted_ids.include?(attributes["id"])
next if (selected_tags - automatic_tag_names(attributes["id"])).any?
if query_text
name_match = attributes["name"]&.downcase&.include?(query_text)
desc_match = attributes["description"]&.downcase&.include?(query_text)
next unless name_match || desc_match
end
record = new(attributes.slice("id", "sql", "name", "description"))
record.user_id = Discourse::SYSTEM_USER_ID.to_s
record
end
end
def tag_names
(self.class.automatic_tag_names(id) + tags.map(&:name)).uniq
end
private
# for `Query.unscoped.find`
class ActiveRecord_Relation
def find(id)
return super unless Query.is_default_query?(id)
QueryFinder.find(id)
end
end
end
end
# == Schema Information
#
# Table name: data_explorer_queries
#
# id :bigint not null, primary key
# description :text
# hidden :boolean default(FALSE), not null
# last_run_at :datetime
# name :string
# sql :text default("SELECT 1"), not null
# created_at :datetime not null
# updated_at :datetime not null
# user_id :integer
#