如何添加条件，其中在轨条款 [英] How to add conditional where clauses in rails

问题描述

我是一个轨道新手，我试图完成与轨道表的检索，而我只是用我的SQL知识来做到这一点。但是，这只是不好像栏杆或红宝石甚至...

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

修改

user.rb

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

place.rb

place.rb

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

container.rb

container.rb

belongs_to :label
belongs_to :place
belongs_to :user

label.rb

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

我的问题是：我怎样才能做到这一点的轨道的方法吗？我看了看<一href="http://guides.rubyonrails.org/active_record_querying.html">http://guides.rubyonrails.org/active_record_querying.html也许我可以用不同的命令这里的某个地方......但这种关系模型只是似乎有点复杂，用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?

感谢您

推荐答案

您可以将多个其中，来电查询，所以你可以建立自己的基础查询：

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

每个其中，通话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)

是一样的话说其中A和B和C 。

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