如何在rails中添加条件where子句

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...

我是一个铁杆新手，我正试图在带有rails的桌面上进行搜索，而我只是使用我的sql知识来做到这一点。但这似乎不像铁路或红宝石甚至......

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

有没有更好的方法来做我正在做的事情？（基本上，只有在填充时才将日期参数传递给sql）

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

EDIT

编辑

user.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

container.rb

belongs_to :label
belongs_to :place
belongs_to :user

label.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".

这两个日期可能都是零，所以我需要在我的“查询”中解决这个问题。

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?

我的问题是：我怎样才能以轨道方式做到这一点？我看了一下http://guides.rubyonrails.org/active_record_querying.html，也许我可以在某处使用except命令......但是这个关系模型看起来有点复杂，用ActiveRecord做这个...怎么可能我？，我真的认为我应该使用ActiveRecord，但是怎么样？

Thank you

谢谢

2 个解决方案

#1

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)

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

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

每个调用都使用AND为您的整体WHERE子句添加另一个部分，如下所示：

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

is the same as saying WHERE a AND b AND c.

与说哪个AND b和c相同。

#2

I cant think of a great reason why you would actually want to generate SQL in your code. Active record seems like a much more efficient solution for your needs, unless there is a reason why you cant use that.

我想不出为什么你真的想在你的代码中生成SQL的一个很好的理由。积极记录似乎是一种更有效的解决方案，满足您的需求，除非您有理由不能使用它。

Link explaining how to join tables with active record

链接说明如何将表与活动记录连接

#1