acts-as-taggable-on multiple context via meta_search generate bad sql query

[tomash]

(cross-issue with acts-as-taggable-on, filed there as well: mbleigh/acts-as-taggable-on#273 )

I don't know if it's a bug in acts-as-taggable-on or meta_search, please advise.

The problem: a given model has three different tagging contexts:

acts_as_taggable_on :ship_code_tags, :port_code_tags, :interest_tags

There's a filtering form built using meta_search which allows filtering by those tags:

<% f.checks :port_code_tag_taggings_tag_name_in, ports.map{|port| [port['name'], port['code']] } do |check| %>
  <dd>
    <%= check.box %>
    <%= check.label %>
  </dd>
<% end %>
<% f.checks :interest_tag_taggings_tag_name_in, interests.all.map {|ti| [ti.name, ti.id.to_s]} do |check| %>
  <dd>
    <%= check.box %>
    <%= check.label %>
  </dd>
<% end %>

Now while this search form properly filters when ONE of the tag-filters is used (i.e. only "port" has some checked checkboxes or only "interest", with the other having all empty checkboxes), it generates bad query when trying to check a checkbox in both contexts:

  Parameters: {"utf8"=>"✓", "search"=>{"port_code_tag_taggings_tag_name_in"=>["AMS"], "interest_tag_taggings_tag_name_in"=>["6"]}}
  TravelmateRequest Load (0.3ms)  SELECT DISTINCT(travelmate_requests.id), travelmate_requests.* 
  FROM `travelmate_requests` 
  LEFT OUTER JOIN `taggings` ON `taggings`.`taggable_id` = `travelmate_requests`.`id` AND taggings.context = 'port_code_tags' AND `taggings`.`taggable_type` = 'TravelmateRequest' 
  LEFT OUTER JOIN `tags` ON `tags`.`id` = `taggings`.`tag_id` 
  LEFT OUTER JOIN `taggings` `interest_tag_taggings_travelmate_requests` ON `interest_tag_taggings_travelmate_requests`.`taggable_id` = `travelmate_requests`.`id` AND taggings.context = 'interest_tags' AND `interest_tag_taggings_travelmate_requests`.`taggable_type` = 'TravelmateRequest' 
  LEFT OUTER JOIN `tags` `tags_taggings` ON `tags_taggings`.`id` = `interest_tag_taggings_travelmate_requests`.`tag_id` 
  WHERE `tags`.`name` IN ('AMS') AND `tags_taggings`.`name` IN ('6') 
  ORDER BY `travelmate_requests`.`created_at` DESC LIMIT 10 OFFSET 0

This SQL query returns empty result set, because in the third LEFT OUTER JOIN "taggings" is used (which refers to a join for the other context) instead of interest_tag_taggings_travelmate_requests.

After rewriting this query to (only this one table name/alias changed) it returns exactly what's expected, i.e. records fulfilling both criteria:

SELECT DISTINCT(travelmate_requests.id), travelmate_requests.* 
  FROM `travelmate_requests` 
  LEFT OUTER JOIN `taggings` ON `taggings`.`taggable_id` = `travelmate_requests`.`id` AND taggings.context = 'port_code_tags' AND `taggings`.`taggable_type` = 'TravelmateRequest' 
  LEFT OUTER JOIN `tags` ON `tags`.`id` = `taggings`.`tag_id` 
  LEFT OUTER JOIN `taggings` `interest_tag_taggings_travelmate_requests` ON `interest_tag_taggings_travelmate_requests`.`taggable_id` = `travelmate_requests`.`id` AND `interest_tag_taggings_travelmate_requests`.context = 'interest_tags' AND `interest_tag_taggings_travelmate_requests`.`taggable_type` = 'TravelmateRequest' 
  LEFT OUTER JOIN `tags` `tags_taggings` ON `tags_taggings`.`id` = `interest_tag_taggings_travelmate_requests`.`tag_id` 
  WHERE `tags`.`name` IN ('AMS') AND `tags_taggings`.`name` IN ('6') 
  ORDER BY `travelmate_requests`.`created_at` DESC LIMIT 10 OFFSET 0

and it works like a charm.

Also, it's worth noting that acts-as-taggable-on built-in named scopes work properly:

1.9.3p194 :015 > TravelmateRequest.tagged_with('AMS', :on => :port_code_tags)
=> records that have given Port Code tags
1.9.3p194 :015 >  TravelmateRequest.tagged_with(6, :on => :interest_tags) works as well
=> records that have given Interest ID tags
1.9.3p194 :015 >TravelmateRequest.tagged_with('AMS', :on => :port_code_tags).tagged_with(6, :on => :interest_tags)
=> records that have both given Port Code and Interest tags

Is it a meta_search issue? Any idea where I could start?

[jacobzhou]

I have the same issue..

[klausistblau]

same here