如何在rails中添加条件where子句 [英] How to add conditional where clauses in rails

问题描述

我是 Rails 新手，正在尝试使用 Rails 对表执行搜索，而我只是使用我的 sql 知识来执行此操作.但这看起来不像是 rails 或 ruby​​...

I am a rails newbie and am trying to perform a search on a table with rails, and i'm just using my sql knowledge to do this. But this just doesn't seems like rails or ruby even...

有没有更好的方法来做我在下面做的事情?(基本上，只有在填写日期参数时才将日期参数传递给 sql)

Is there any better way to do what i'm doing below? (basically, only pass date arguments to sql if they are filled)

def search(begin_date=nil, end_date=nil)

    subject = " and created_at "

    if !(begin_date.nil? || end_date.nil?)
      where_part = subject + "BETWEEN :begin_date AND :end_date"
    else if (begin_date.nil? && end_date.nil?) 
      where_part = ""
    else if(begin_date.nil?)
      where_part = subject + " <= :end_date"
    else if (end_date.nil?)
      where_part = subject + " >= :begin_date"
    end
    end
    end
    end

    User.joins(places: {containers: {label: :user}}).where("users.id= :user_id "+where_part, user_id: self.id, begin_date:begin_date, end_date:end_date).group(...).select(...)
end

编辑

用户.rb

has_many :containers
has_many :user_places
has_many :places, through: :user_places
has_many :labels

place.rb

has_many :containers
has_many :user_places
has_many :users, through: :user_places

容器.rb

belongs_to :label
belongs_to :place
belongs_to :user

标签.rb

belongs_to :user
has_many :containers

基本上，我想计算给定用户标签内或与每个位置有直接关系的容器数量，并希望能够按开始和结束日期对其进行过滤.

Basically, i want to get a count of the number of containers within a given user's labels or with a direct relationship, per location, and want to be able to filter it by begin and end dates.

这两个日期都可能为零，所以我需要在我的查询"中解决这个问题.

Either of this dates may be nil, and so i would need to address this in my "query".

我的问题是:我怎样才能做到这一点?我看了看 http://guides.rubyonrails.org/active_record_querying.html 也许我可以在某处使用 except 命令……但是这种关系模型似乎有点复杂，用 ActiveRecord 来做这件事……我可以怎么做?，我真的认为我应该使用 ActiveRecord，但是如何?

My question is : How can i do this the rails way? I took a look at http://guides.rubyonrails.org/active_record_querying.html and perhaps i could use the except command here somewhere...but this relationship model just seems a bit complex to do this with ActiveRecord...how may I?, i really think i should use ActiveRecord, but how?

谢谢

推荐答案

您可以对查询应用多个 where 调用，以便构建基本查询:

You can apply multiple where calls to a query so you can build your base query:

query = User.joins(...)
            .group(...)
            .select(...)
            .where('users.id = :user_id', :user_id => self.id)

然后根据您的日期间隔添加另一个 where 调用:

and then add another where call depending on your date interval:

if(begin_date && end_date)
  query = query.where(:created_at => begin_date .. end_date)
  # or where('created_at between :begin_date and :end_date', :begin_date => begin_date, :end_date => end_date)
elsif(begin_date)
  query = query.where('created_at >= :begin_date', :begin_date => begin_date)
elsif(end_date)
  query = query.where('created_at <= :end_date', :end_date => end_date)
end

每个 where 调用都会使用 AND 将另一部分添加到您的整体 WHERE 子句中，例如:

Each where call adds another piece to your overall WHERE clause using AND so something like:

q = M.where(a).where(b).where(c)

等同于说WHERE a AND b AND c.

这篇关于如何在rails中添加条件where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！