Jazz Jazz - 2 months ago 15
Ruby Question

Ruby on Rails: Show results between 2 dates

I am using jquery date picker to pick two dates when searching my database. At the moment the search will only search on an exact start date and exact end date, but I want the user to be able to pick two dates, where they can search between the dates.

Here is my model:

class Project < ActiveRecord::Base
attr_accessible :business_div, :client, :customer_benifits, :edited_date, :end_date, :entry_date, :exception_pm, :financials, :industry, :keywords, :lessons_learned, :project_name, :project_owner, :role, :start_date, :status, :summary, :tech

validates_presence_of :business_div, :client, :customer_benifits, :end_date, :exception_pm, :financials, :industry, :keywords, :lessons_learned, :project_name, :project_owner, :role, :start_date, :status, :summary, :tech





def self.search(search_client, search_industry, search_role, search_tech, search_business_div, search_project_owner, search_exception_pm, search_status, search_start_date, search_end_date, search_keywords)
return scoped unless search_client.present? || search_industry.present? || search_role.present? || search_tech.present? || search_business_div.present? || search_project_owner.present? || search_exception_pm.present? || search_status.present? || search_start_date.present? || search_end_date.present? || search_keywords.present?

if search_start_date != ""
search_start_date = Date.parse(search_start_date).strftime("%Y-%m-%d")
end
if search_end_date != ""
search_end_date = Date.parse(search_end_date).strftime("%Y-%m-%d")
end



where(['client LIKE ? AND industry LIKE ? AND role LIKE ? AND tech LIKE ? AND business_div LIKE ? AND project_owner LIKE ? AND exception_pm LIKE ? AND status LIKE ? AND start_date LIKE ? AND end_date LIKE ? AND keywords LIKE ?', "%#{search_client}%", "%#{search_industry}%" , "%#{search_role}%" , "%#{search_tech}%" , "%#{search_business_div}%" , "%#{search_project_owner}%" , "%#{search_exception_pm}%" , "%#{search_status}%", "%#{search_start_date}%", "%#{search_end_date}%","%#{search_keywords}%"])



end


def self.paginated_for_index(projects_per_page, current_page)
paginate(:per_page => projects_per_page, :page => current_page)
end

end


Here is my search view:

<h1>Search</h1>

<% if @project_search.total_entries > 0 %>
<%= form_tag search_path, method: :get do %>

Client :
<%= select(@projects, :client, Project.all.map {|p| [p.client]}.uniq, :prompt => "-Any-", :selected => params[:client]) %></br>

Industry :
<%= select(@projects, :industry, Project.all.map {|p| [p.industry]}.uniq, :prompt => "-Any-", :selected => params[:industry]) %></br>

Role :
<%= select(@projects, :role, Project.all.map {|p| [p.role]}.uniq, :prompt => "-Any-", :selected => params[:role]) %></br>

Technologies :
<%= select(@projects, :tech, Project.all.map {|p| [p.tech]}.uniq, :prompt => "-Any-", :selected => params[:tech]) %></br>

Business Division :

<%= select(@projects, :business_div, Project.all.map {|p| [p.business_div]}.uniq, :prompt => "-Any-", :selected => params[:business_div]) %></br>

Project Owner :
<%= select(@projects, :project_owner, Project.all.map {|p| [p.project_owner]}.uniq, :prompt => "-Any-", :selected => params[:project_owner]) %></br>

Exception PM
<%= select(@projects, :exception_pm, Project.all.map {|p| [p.exception_pm]}.uniq, :prompt => "-Any-", :selected => params[:exception_pm]) %></br>


Start Date :

<%= text_field_tag("start_date") %></br>


End Date :

<%= text_field_tag("end_date") %></br>


Status :

<%= select(@projects, :status, Project.all.map {|p| [p.status]}.uniq, :prompt => "-Any-", :selected => params[:status]) %></br>

Keywords :

<%= text_field_tag :keywords, params[:keywords] %></br>

<%= submit_tag "Search", name: nil %>

<% end %>


<% if @search_performed %>
<h3><%=@project_search.total_entries%> results</h3>



<table class = "pretty">
<table border="1">
<tr>
<th><%= sortable "project_name", "Project name" %> </th>
<th><%= sortable "client", "Client" %></th>
<% if false %>
<th>Exception pm</th>
<th>Project owner</th>
<% end %>
<th><%= sortable "tech", "Technologies" %></th>
<th><%= sortable "role", "Role" %></th>
<th><%= sortable "industry", "Industry" %></th>
<% if false %>
<th>Financials</th>
<th>Business div</th>
<th>Status</th>
<th>Start date</th>
<th>End date</th>
<th>Entry date</th>
<th>Edited date</th>
<th>Summary</th>
<th>Lessons learned</tStackh>
<th>Customer benifits</th>
<th>Keywords</th>
<!th></th>
<!th></th>
<!th></th>
<% end %>
</tr>



<% @project_search.each do |t| %>
<tr>
<td><%= t.project_name %></td>
<td><%= t.client %></td>
<% if false %>
<td><%= t.exception_pm %></td>
<td><%= t.project_owner %></td>
<% end %>
<td><%= t.tech %></td>
<td><%= t.role %></td>
<td><%= t.industry %></td>
<% if false %>
<td><%= t.financials %></td>
<td><%= t.business_div %></td>
<td><%= t.status %></td>
<td><%= l(t.start_date) if t.start_date? %></td>
<td><%= l(t.end_date) if t.end_date? %></td>
<td><%= t.entry_date %></td>
<td><%= t.edited_date %></td>
<td><%= t.summary %></td>
<td><%= t.lessons_learned %></td>
<td><%= t.customer_benifits %></td>
<td><%= t.keywords %></td>
<% end %>
<td><%= link_to 'Show', t %></td>
<!td><%#= link_to 'Edit', edit_project_path(project) %></td>
<!td><%#= link_to 'Destroy', project, method: :delete, data: { confirm: 'Are you sure?' } %></td>
</tr>
<% end %>
</table>
<%end %>

<% if @search_performed %>
Results per page: <%= select_tag :per_page, options_for_select([10,20,50], params[:per_page].to_i), :onchange => "if(this.value){window.location='?per_page='+this.value;}" %>

<% end %>

<% else %>
<h2> Sorry, there are no results matching your search. Please try again. </h2>
<% end %>
<br />
<%# end %>

<% if @search_performed %>
<%= hidden_field_tag :direction, params[:direction] %>
<%= hidden_field_tag :sort, params[:sort] %>
<%= hidden_field_tag :per_page, params[:per_page] %>
<%= hidden_field_tag :page, params[:page] %>

<%= will_paginate (@project_search) %>


<%= button_to "Search Again?", search_path, :method => "get" %>

<% end %>
<%= button_to "Home", projects_path, :method => "get" %>


and here is part of my controller:

def search

@search = params[:client], params[:industry], params[:role], params[:tech], params[:business_div], params[:project_owner], params[:exception_pm], params[:status], params[:start_date], params[:end_date], params[:keywords]

@project_search = Project.search(*@search).order(sort_column + ' ' + sort_direction).paginated_for_index(per_page, page)

@search_performed = !@search.reject! { |c| c.blank? }.empty?

@project = Project.new(params[:project])

respond_to do |format|
format.html # search.html.erb
format.json { render :json => @project }
end

end


Hopefully someone can help me. I'm new to rails so go easy :). Thanks in advance.

Answer

Update: this is what your query should look like:

where(['client LIKE ? AND industry LIKE ? AND role LIKE ? AND tech LIKE ? AND business_div LIKE ? AND project_owner LIKE ? AND exception_pm LIKE ? AND status LIKE ? AND DATE(start_date) BETWEEN ? AND ? AND DATE(end_date) BETWEEN ? AND ? AND keywords LIKE ?', 
      "%#{search_client}%", "%#{search_industry}%" , "%#{search_role}%" , "%#{search_tech}%" , "%#{search_business_div}%" , 
      "%#{search_project_owner}%" , "%#{search_exception_pm}%" , "%#{search_status}%", 
      search_start_date, search_end_date, search_start_date, search_end_date,"%#{search_keywords}%"
    ])

What we are doing is telling MySQL to take the date bit of the start and end dates, and make sure it is between the values we are providing it with.

Hope this helps

Comments