Ruby on Rails: duplicate results when searching

Question

I have a rails app that allow the user to search for projects in a database.

Here is the search function in the project model:

def self.search(search_industry, search_role, search_techs_ids)

    _projects = Project.scoped 

    if search_industry.present?
      _projects = _projects.where ['industry LIKE ?', like(search_industry)]
    end
    if search_role.present?
      _projects = _projects.where ['role LIKE ?', like(search_role)]
    end
    if search_techs_ids.present?
    _projects = _projects.joins(:technols).where("technols.id" => search_techs_ids)
    end
    _projects
    end

and here is part of my search page

<div class="tech">
<%= fields_for(@project_technol) do |ab| %>
 Technologies : 
 <%     tech_ids = params[:technols][:id].reject(&:blank?) unless params[:technols].nil? %>

<%if params[:technols].nil?%>

<%= collection_select(:technols, :id, @all_technols, :id, :tech, {}, {:multiple => true} ) %>

<% else %>

<%= collection_select(:technols, :id, @all_technols, :id, :tech, {}, {:multiple => true, :selected => tech_ids } ) %>
<% end %>

</div>

Here is my search action:

def search

    tech_ids = params[:technols][:id].reject(&:blank?) unless params[:technols].nil?

    @search =  params[:industry], params[:role], tech_ids

    @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])

    @all_technols = Technol.all

    @project_technol = @project.projecttechnols.build

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

end

My problem is that when I select all the technologies in the collection_select on the search page and click search, the result seems to show duplicates all of the records, if they hold more than one of the technologies.

For example, there are 3 projects in the database. P1, P2, P3, and all have 3 technologies, T1, T2, and T3. If I was to search on all 3 technologies, every project should show up once, but instead, they show up 3 times.

Does anyone have any ideas where I might be going wrong. Thanks in advance.

EDIT

My params when searching:

Parameters: {"utf8"=>"✓", "client"=>"", "industry"=>"", "technols"=>{"id"=>["", "40", "41", "42", "43", "44", "46", "47", "48", "49", "50", "51", "52", "53", "54"]}, "start_date_dd"=>"", "start_date_A"=>"", "start_date_B"=>"", "status"=>"", "per_page"=>"10"}
  User Load (0.4ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1
  Technol Load (0.3ms)  SELECT "technols".* FROM "technols" 
  Project Load (0.8ms)  SELECT "projects".* FROM "projects" ORDER BY client
  Project Load (0.4ms)  SELECT "projects".* FROM "projects" ORDER BY industry
  Project Load (0.4ms)  SELECT "projects".* FROM "projects" ORDER BY status
   (1.0ms)  SELECT DISTINCT COUNT(*) FROM "projects" INNER JOIN "projecttechnols" ON "projecttechnols"."project_id" = "projects"."id" INNER JOIN "technols" ON "technols"."id" = "projecttechnols"."technol_id" WHERE "technols"."id" IN (40, 41, 42, 43, 44, 46, 47, 48, 49, 50, 51, 52, 53, 54)
  Project Load (13.2ms)  SELECT DISTINCT "projects".* FROM "projects" INNER JOIN "projecttechnols" ON "projecttechnols"."project_id" = "projects"."id" INNER JOIN "technols" ON "technols"."id" = "projecttechnols"."technol_id" WHERE "technols"."id" IN (40, 41, 42, 43, 44, 46, 47, 48, 49, 50, 51, 52, 53, 54) ORDER BY project_name asc LIMIT 10 OFFSET 0
  Technol Load (0.9ms)  SELECT "technols".* FROM "technols" INNER JOIN "projecttechnols" ON "technols"."id" = "projecttechnols"."technol_id" WHERE "projecttechnols"."project_id" = 107
  Technol Load (1.8ms)  SELECT "technols".* FROM "technols" INNER JOIN "projecttechnols" ON "technols"."id" = "projecttechnols"."technol_id" WHERE "projecttechnols"."project_id" = 100
  Technol Load (1.8ms)  SELECT "technols".* FROM "technols" INNER JOIN "projecttechnols" ON "technols"."id" = "projecttechnols"."technol_id" WHERE "projecttechnols"."project_id" = 106
  Rendered projects/search.html.erb within layouts/application (68.2ms)
Completed 200 OK in 100ms (Views: 74.8ms | ActiveRecord: 22.8ms)

UPDATE:

I have just found an error.

My model now includes this code;

if search_techs_ids.present?
  _projects = _projects.includes(:technols).where("technols.id" => search_techs_ids)
end

Which doesn't show duplicates anymore, but I noticed when I search for a single technology, in the table that displays the results. I have this column:

<td><ul>
  <% t.technols.each do |technol| %>
    <li><%= technol.tech %><!/li>
  <% end %>
</ul></td>

This used to show the list of all the technologies for that project, but now with the new code, it just displays the one that I searched for. Any ideas?

This is why the uniq filter doesn't work it work flawlessly in mysql, see http://stackoverflow.com/questions/9758793/rails-3-activerecord-postgresql-uniq-command-doesnt-work — tommasop, Oct 10 '12 at 09:47
I have asked a new question which includes t object. http://stackoverflow.com/questions/12817954/ruby-on-rails-after-search-list-not-displaying-all-data/12818657#12818657 — Jazz, Oct 10 '12 at 12:50

score 2 · Accepted Answer · edited May 23 '17 at 12:12

2

can you try using includes instead of joins?

if search_techs_ids.present?
  _projects = _projects.includes(:technols).where("technols.id" => search_techs_ids)
end

The includes option basically does an outer join instead of an inner join. It also eager loads the records this means you will have a "bigger" single query instead of multiple queries.

See here for a good explanation

Ruby on Rails: duplicate results when searching

1 Answers1