RSpec: Bogus SQL Error?

Question

I have a Photo model with the following method to search for associated tags by name:

class Photo < ActiveRecord::Base
  has_many :taggings, :dependent => :destroy
  has_many :tags, :through => :taggings 
  ...

  def self.tagged_with( string )
    array = string.split(',').map{ |s| s.lstrip }
    joins(:tags).where('tags.name' => array ).group(:id)
  end

  ...
end

If I use this in the console it yields exactly what I would expect:

Photo.tagged_with('foo, bar, baz')
# Returns unique photos with tags named foo, bar, or baz

However, I tried to build this using a test in RSpec, and the test fails. Here's my test:

describe "tags" do
  it "should return a list of photos matching a string of tags" do
    t1 = Tag.create(:name=>'test')
    t2 = Tag.create(:name=>'bar')
    t1.photos << Photo.find(1,2,3)
    t2.photos << Photo.find(3,4)
    t1.save
    t2.save

    Photo.tagged_with('test').should have(3).photos
    Photo.tagged_with('bar').should have(2).photos
    Photo.tagged_with('test, bar').should have(4).photos
  end
end

This test fails with the following error:

  1) Photo tags should return a list of photos matching a string of tags
     Failure/Error: Photo.tagged_with('test').should have(3).photos
     ActiveRecord::StatementInvalid:
       SQLite3::SQLException: ambiguous column name: id: SELECT COUNT(*) AS count_all, id AS id FROM "photos" INNER JOIN "taggings" ON "photos"."id" = "taggings"."photo_id" INNER JOIN "tags" ON "tags"."id" = "taggings"."tag_id" WHERE "tags"."name" IN ('test') GROUP BY id
     # ./spec/models/photo_spec.rb:84:in `block (3 levels) in <top (required)>'

So, the code works but the test fails. What am I doing wrong in my test?

Answer 1

Seems like it's complaining because you're grouping by id and both the photos and taggings tables have ids (The database doesn't know if you mean photos.id or taggings.id, hence the 'ambiguous' error). Try changing .group(:id) to .group('photos.id') in your tagged_with method.

Answer 2

To fix this error, be explicit on what you want to group by, so you would do:

joins(:tags).where('tags.name' => array ).group("photos.id")

Even if your code works now, with this query you really do want to be explicit. This is becuase all the tables that you are dealing with(taggings, and photos) both have an ID column.