大于/低于工作，但在>=上不等于，在.where()上不等于

Question

我有一个Rails 4应用程序，我试图用3个可选参数对我的发票进行简单搜索:客户机名称、开始日期、结束日期。

如果我将开始日期和结束日期放在< and >上，那么搜索基本上可以正常工作，但是即使我使用了>=和<=，如果发票日期与开始日期或结束日期相同，那么它就不会显示在结果列表中。

所使用的表如下所示：

    Client Table
       ID
       Name
       The rest of the fields aren't necessary

    Invoice Table
       ID
       Client_ID
       Total_Price
       Created_At *only here for relevance*

我的发票控制器搜索方法如下所示：

    def search
      if request.post?
        @count = 0
        @invoices = Invoice.all
        if params[:start_date].present?
          @invoices = Invoice.invoices_by_date(@invoices, params[:start_date], 'start')
          if @invoices.present?
            @count = 1
          else
            @count = 2
          end
        end
        if params[:end_date].present?
          @invoices = Invoice.invoices_by_date(@invoices, params[:end_date], 'end')
          if @invoices.present?
            @count = 1
          else
            @count = 2
          end
        end
        if params[:name].present?
          @invoices = Invoice.invoices_by_client(@invoices, params[:name])
          if @invoices.present?
            @count = 1
          else
            @count = 2
          end
        end
        if @count == 2
          flash.now[:danger] = "No results found."
          @invoices = nil
        end
        @name = params[:name]
        @start_date = params[:start_date]
        @end_date = params[:end_date]
      end
    end

我使用的发票模型方法如下所示：

    def self.invoices_by_client(invoices, name)
      invoices= invoices.includes(:client)
                       .select('invoices.created_at', 'invoices.total_price', 'clients.name')
                       .where("clients.name LIKE ?", "%#{name}%")
                       .references(:client)
      return invoices
    end

    def self.invoices_by_date(invoices, date, modifier)
      if modifier == 'start'
        invoices = invoices.includes(:client)
                         .select('invoices.created_at', 'invoices.total_price', 'clients.name')
                         .where("invoices.created_at >= ?", date)
                         .references(:client)
      elsif modifier == 'end'
        invoices = invoices.includes(:client)
                         .select('invoices.created_at', 'invoices.total_price', 'clients.name')
                         .where("invoices.created_at <= ? ", date)
                         .references(:client)
      end
      return invoices
    end

总的来说，这可能不是最好的解决方案，我不知道我是否做错了什么，所以如果你们能帮我解决这个问题，那就太好了。

Answer 1

我听从了亚历杭德罗的建议，把时间和日期搞得一团糟，就像这样：

    if modifier == 'start'
      invoices = invoices.includes(:client)
                         .select('invoices.created_at', 'invoices.total_price', 'clients.name')
                         .where("invoices.created_at >= ?", "#{date} 00:00:00") // Added the start time
                         .references(:client)
     elsif modifier == 'end'
       invoices = invoices.includes(:client)
                          .select('invoices.created_at', 'invoices.total_price', 'clients.name')
                          .where("invoices.created_at <= ? ", "#{date} 23:59:59") // Added end time aswell
                          .references(:client)
     end

我强迫开始日期的时间为00:00:00，而结束日期的时间为23:59:59，并且它按需要工作。谢谢你的帮助，我希望这能帮助到其他人！