添加“和”和“或”子句通过rails查询界面查询
问题描述:
我想通过Rails查询界面运行下面的查询,但无法翻译我的逻辑。查询是添加“和”和“或”子句通过rails查询界面查询
Select f.* from feeds f
Left join feed_items fi on fi.id = f.feedable_id
where
f.feedable_type in ('Homework', 'Datesheet')
and
(
(fi.assignable_type = 'Level' and assignable_id IN (1)) or
(fi.assignable_type = 'Student' and assignable_id IN (1)) or
(fi.assignable_type = 'Section' and assignable_id IN (1))
)
场景:
我收到以下PARAMS散列包含将动态在我的查询
{"page"=>"1", "limit"=>"2", "type_filter"=>["Homework", "Datesheet"], "assignable_filter"=>{"Student"=>"[2]", "Section"=>"[1]", "Level"=>"[1]"}}
到目前为止,加入过滤器,我的动作我做了什么正在加入表格并添加where子句的类型过滤器,但不知道如何动态添加assignable_filters。这里是我的Rails代码,options
是params
在下面的代码
def get_feeds(options)
base = Feed.includes(:feed_item)
base = add_type_filters base, options
base = add_assignable_filters base, options
format_response base, options
end
def add_type_filters(base, options)
type_filter = options[:type_filter]
if !type_filter.nil? and type_filter.length > 0
base = base.where('feedable_type IN (?)', options[:type_filter])
end
base
end
def add_assignable_filters(base, options)
assignable_filter = options[:assignable_filter]
if !assignable_filter.nil?
assignable_filter.each do |key, value|
# code for adding filters combined with or conditions
end
# wrap the or conditions and join them with an and in main where clause
end
base
end
P.S我用导轨5
答
有动态生成的查询没有直接的方法。我必须构建解决问题的字符串。我目前的解决方案是
def get_feeds(options)
params_hash = {}
type_filters = add_type_filters options, params_hash
assignable_filters = add_assignable_filters options, params_hash
where = type_filters
where = where ? "#{where} and (#{assignable_filters})" : assignable_filters
base = Feed.eager_load(:feed_item).where(where, params_hash)
format_response base, options
end
def add_type_filters(options, params_hash)
type_filter = options[:type_filter]
type_filter_sql = nil
if !type_filter.nil? and type_filter.length > 0
type_filter_sql = 'feeds.feedable_type in (:type_filter)'
params_hash[:type_filter] = type_filter
end
type_filter_sql
end
def add_assignable_filters(options, params_hash)
assignable_filter_sql = []
assignable_filter = options[:assignable_filter]
if !assignable_filter.nil?
assignable_filter.each do |key, value|
assignable_filter_sql.push("(feed_items.assignable_type = '#{key}' and feed_items.assignable_id IN (:#{key}))")
params_hash[key.to_sym] = JSON.parse(value)
end
end
assignable_filter_sql.join(' or ')
end
我注意到的第一件事:是不是feed和feed_items(一对多关系?)。如果是'Feed.includes(:feed_items)'是正确的。 – haffla
在这里您可以看到如何在Rails5中执行OR查询:https://*.com/questions/32753168/rails-5-activerecord-or-query – haffla
@haffla其一对一关联 –