ruby-on-rails - 如何使用 rails 和 postgres 改进查询
问题描述
我有三个模型posts
有一个 post_rating
,post_waiting_time
post.rb
name_column type example
id integer 20
name string 'welcome'
user_id integer 1
post_rating.rb
name_column type example
id integer 10
rating_label enum poor,average,great
post_id integer 20
post_waiting_time.rb
name_column type example
id integer 10
waiting_label enum 0-3,4-6,7-10
post_id integer 20
我尝试在查询使用中使用 rating_label、waiting_label eager_load
,joins
但不能在查询中写入多标签
posts_lists = user.posts.eager_load(:post_rating, :post_waiting_time).where("post_waiting_times.waiting_label = ? ", '0-3')
此查询适用于一个值,但我需要多个查询使用 waiting_label 例如0-3, 4-7
我尝试使用IN() but i have error
Post.eager_load(:post_rating, :post_waiting_time).where('post_waiting_times.waiting_label IN( ? )', 'more_30,0-
3')
最后一次查询的错误信息
ActiveRecord::StatementInvalid (PG::InvalidTextRepresentation: ERROR: invalid input value for enum tag_label: "more_30,0-3")
LINE 1: ...ERE (post_waiting_times.waiting_label IN( 'more_30,0...
我通过编写过滤器查询来解决此问题rating_label
,waiting_label
posts_lists = user.posts
posts_lists.each do |record|
flag_filter = false
unless waiting_filter.empty?
flag_filter = if waiting_filter.include?(record.post_waiting_time.waiting_label)
true
else
false
end
end
unless rating_filter.empty?
flag_filter = if rating_filter.include?(record.post_rating.rating_label)
true
else
false
end
end
if flag
puts record
end
但这不是效率的方式
我用Rails 6
,PostgreSQL
解决方案
你做错了,你可以直接在rails中传递一个数组进行比较。这是更新的查询
Post.eager_load(:post_rating, :post_waiting_time).where(post_waiting_times: {waiting_label: ['more_30','0-3']})
希望这能解决问题。