CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlboolean
Jazz
Jazz

Reputation: 1090

Ruby on Rails: Searching with a boolean - postgres

I have an app that allows users to enter, edit and search for projects in a database.

In my search function, you can do complex searches by adding params to the search.

def self.like(text); "%#{text}%"; end

  def self.search(search_archive, search_client, search_status, search_fullname)
    # start with a scoped query, to apply more scopes on it afterwards
    _projects = Project.scoped 
    # then, for each of the parameters, apply the scope only if present

    if search_archive.present?
      _projects = _projects.where ['archive LIKE ?', like(search_archive)] 
    end
    if search_client.present?
      _projects = _projects.where ['client LIKE ?', like(search_client)] 
    end

     if search_status.present?
      _projects = _projects.where ['status LIKE ?', like(search_status)]
    end

if search_fullname.present?
      _projects = _projects.where ['fullname LIKE ?', like(search_fullname)]
    end
    # now you have applied only the present scopes. return the result, and watch 
    # the query as it executes.

    _projects

  end

Now I recently added the option to archive the project, that goes into the project table as a boolean.

I know my search function is wrong as I don't think I can deal with booleans they way I have tried.

if search_archive.present?
          _projects = _projects.where ['archive LIKE ?', like(search_archive)] 
        end

Here is the error:

PG::Error: ERROR:  operator does not exist: boolean ~~ unknown
LINE 1: SELECT COUNT(*) FROM "projects"  WHERE (archive LIKE '%{"{:c...

Any ideas? Thanks in advance.

UPDATE:

Changing to the suggestion below, 

if search_archive.present?
  _projects = _projects.where(:archive => search_archive)
end

I get this error:

PG::Error: ERROR:  missing FROM-clause entry for table "archive"
LINE 1: SELECT COUNT(*) FROM "projects"  WHERE "archive"."{:class=>"...
                                               ^
: SELECT COUNT(*) FROM "projects"  WHERE "archive"."{:class=>""archive""}" = '1'

Here is my search action:

def search


    @search = params[:archive], params[:client], params[:status], params[:fullname]

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

    @search_performed = [email protected]! { |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

ADDING PART OF SEARCH VIEW

<%= form_tag search_path, method: :get do %>

<% if current_user.try(:admin?) %>
Archive:
<%= check_box :archive, :class => "archive" %></H2>
<% end %>
<% if !current_user.try(:admin?) %>
<%= hidden_field :archive, :value => false %>
<% end %>

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

<div class="buttons">
<div id="search_button">
<%= submit_tag "Search", name: nil, :class => "button" %>
</div>

<% end %>

EXPLANATION

If I am an Admin, and I leave the archive check box unchecked, I want to see all projects that aren't archived. If I check it, I only want to see the archived ones, included with other params I search on.

If I am a normal user, I shouldn't be able to see the archive checkbox, and every search I do should be on projects that have archive false, behind the scenes.

Upvotes: 1

Views: 1176

Answers (2)

Salil
Salil

Reputation: 47462

if archive is Boolean add something like follwing

if search_archive.present?
  _projects = _projects.where(:archive => search_archive != "false")
end

Change

<% if current_user.try(:admin?) %>
Archive:
<%= check_box :archive, :class => "archive" %></H2>
<% end %>
<% if !current_user.try(:admin?) %>
<%= hidden_field :archive, :value => false %>
<% end %>

To

<% if current_user.try(:admin?) %>
  Archive: <%= check_box_tag :archive, true, false, :class => "archive" %></H2>
<% else %>
  <%= hidden_field :archive, :value => false %>
<% end %>

Upvotes: 2

sren
sren

Reputation: 3623

If archive is a boolean, and you want to search for either true or false on it, and search_archive is also a boolean, this is all you need to do:

if search_archive.present?
  _projects = _projects.where(:archive => search_archive)
end

UPDATE:

Your checkbox syntax is wrong, needs to be something like:

check_box("project", "archive", {:class => 'archive'})

Upvotes: 2

Related Questions