drnic/composite_primary_keys — Issue #81: ambiguous column names via build_count_subquery
Author: cleesmith
Created: 2012-01-25 08:03:19+00:00
State: open

Using rails 3.2.0 and composite_primary_keys 5.0.0.rc1 (and previous versions), I have the following:

```ruby
class Event < ActiveRecord::Base
self.pluralize_table_names = false
set_primary_keys [:sid, :cid]
belongs_to :iphdr, foreign_key: [:sid, :cid], :dependent => :destroy
```

```ruby
class Iphdr < ActiveRecord::Base
self.pluralize_table_names = false
set_primary_keys [:sid, :cid]
has_many :events, foreign_key: [:sid, :cid]
```
These queries

```ruby
@events = Event.includes(:iphdr).where("iphdr.ip_src = ?", 329777412).count
```
... or ...

```ruby
@events = Event.includes(:iphdr).where("iphdr.ip_src = ?", 329777412).size
```
cause the following error:

```
Mysql2::Error: Column 'sid' in field list is ambiguous:
SELECT COUNT(*) FROM (SELECT DISTINCT sid,cid
FROM `event` LEFT OUTER JOIN `iphdr`
ON `iphdr`.`sid` = `event`.`sid`
AND `iphdr`.`cid` = `event`.`cid`
WHERE (iphdr.ip_src = 329777412)) subquery_for_count
```

As both tables have the same primary keys, I think the **SELECT DISTINCT sid,cid**
should be **SELECT DISTINCT event.sid, event.cid**. After forking CPK I changed the
following in **build_count_subquery** method in **lib/composite_primary_keys/relation/calculations.rb**:
... from:

```ruby
relation.select_values = ["DISTINCT #{column.to_s}"]
```
... to this:

```ruby
if column_name.is_a?(CompositePrimaryKeys::CompositeKeys)
relation.select_values = ["DISTINCT #{column.to_s.split(',').collect {|col| @klass.unscoped.table.name + '.' + col.strip}.join(', ')}"]
else
relation.select_values = ["DISTINCT #{column.to_s}"]
end
```
I am unsure if this is a proper fix, but it works for MySQL ... but maybe there is a better way to express the query.
Thanks for CPK.