description

I'm using DataMapper 0.9.10 and data_objects 0.9.11.

When I have two chained one-to-many associations, e.g. Author -> Post -> Comment,
trying to fetch associated Comments on a Post which has *itself* been fetched
from an Author results in some strange SQL being generated and some incorrect behaviour.

Here, I create a single Author, two Posts attached to that Author, and one
Comment associated with each Post, with a :name of "foo", to give us something to
search on. Then I try to retrieve the Comment for the Post with id=2.

The strange behaviour detailed below does not apply if the Post has been fetched by id.
In that case, the generated SQL simply specifies ("post_id" IN (2)).

If I first fetch the Post by the association method on the Author, though:

- The SQL to fetch the associated Comments specifies ("post_id" IN (1, 2))
  rather than ("post_id" = 2).

- This does not seem to affect the output of post.comments,
  post.comments.all, .first, .last etc. post.comments(:name => "foo") and
  post.comments.all(:name => "foo") also work fine.

- However, when I call post.comments.first(:name => "foo"), the Comment associated with
  Post #1 is returned! (the line 'got comment #<Comment id=1 name="foo" post_id=1>'
  at the end of the script's output)

In summary, then:

- When a Post has been fetched by association, calling Comment association methods on it
  generates incorrectly inclusive SQL queries.

- This causes post.first to return an incorrect value, but *only* post.first, and *only*
  if it is called with finder parameters.

test_associations.rb

require 'dm-core'

class Author
  include DataMapper::Resource

  property :id, Serial
  property :name, String

  has n, :posts
end

class Post
  include DataMapper::Resource

  property :id, Serial

  belongs_to :author
  has n, :comments
end

class Comment
  include DataMapper::Resource

  property :id, Serial
  property :name, String

  belongs_to :post
end

# mysql adapter
# DataMapper.setup(:default, :adapter => "mysql", :host => "localhost", :username => "root", :password => "", :database => "datamapper_test")
# DataMapper.auto_migrate!

# sqlite3 adapter
DataMapper.setup(:default, 'sqlite3::memory:')
DataMapper.auto_migrate!

logger = DataMapper.logger
logger.set_log(STDOUT, :debug)

# create test data

author = Author.create

post_1 = author.posts.create
post_2 = author.posts.create

comment_on_post_1 = post_1.comments.create(:name => "foo")
comment_on_post_2 = post_2.comments.create(:name => "foo")

# fetch post by id

logger.info "fetching post by id"
post = Post.get(post_2.id)

logger.info "getting comments with .all"
comments = post.comments.all(:name => "foo")
logger.info "got comments #{comments.inspect}"

logger.info "getting comment with .first"
comment = post.comments.first(:name => "foo")
logger.info "got comment #{comment.inspect}"

# fetch post by association

logger.info "fetching post by association"
post = author.posts.last

logger.info "getting comments with .all"
comments = post.comments.all(:name => "foo")
logger.info "got comments #{comments.inspect}"

logger.info "getting comment with .first"
comment = post.comments.first(:name => "foo")
logger.info "got comment #{comment.inspect}"

test_output

$ ruby -rubygems test_associations.rb 
Thu, 29 Jan 2009 13:28:06 GMT ~ debug ~ (0.000000) INSERT INTO "authors" DEFAULT VALUES
Thu, 29 Jan 2009 13:28:06 GMT ~ debug ~ (0.000000) SELECT "id", "author_id" FROM "posts" WHERE ("author_id" IN (1)) ORDER BY "id"
Thu, 29 Jan 2009 13:28:06 GMT ~ debug ~ (0.000000) INSERT INTO "posts" ("author_id") VALUES (1)
Thu, 29 Jan 2009 13:28:06 GMT ~ debug ~ (0.000000) INSERT INTO "posts" ("author_id") VALUES (1)
Thu, 29 Jan 2009 13:28:06 GMT ~ debug ~ (0.000000) SELECT "id", "name", "post_id" FROM "comments" WHERE ("post_id" IN (1, 2)) ORDER BY "id"
Thu, 29 Jan 2009 13:28:06 GMT ~ debug ~ (0.000000) INSERT INTO "comments" ("post_id", "name") VALUES (1, 'foo')
Thu, 29 Jan 2009 13:28:06 GMT ~ debug ~ (0.000000) SELECT "id", "name", "post_id" FROM "comments" WHERE ("post_id" IN (1, 2)) ORDER BY "id"
Thu, 29 Jan 2009 13:28:06 GMT ~ debug ~ (0.000000) INSERT INTO "comments" ("post_id", "name") VALUES (2, 'foo')
Thu, 29 Jan 2009 13:28:06 GMT ~ info ~ fetching post by id
Thu, 29 Jan 2009 13:28:06 GMT ~ debug ~ (0.000000) SELECT "id", "author_id" FROM "posts" WHERE ("id" = 2) ORDER BY "id" LIMIT 1
Thu, 29 Jan 2009 13:28:06 GMT ~ info ~ getting comments with .all
Thu, 29 Jan 2009 13:28:06 GMT ~ debug ~ (0.000000) SELECT "id", "name", "post_id" FROM "comments" WHERE ("name" = 'foo') AND ("post_id" IN (2)) ORDER BY "id"
Thu, 29 Jan 2009 13:28:07 GMT ~ info ~ got comments [#<Comment id=2 name="foo" post_id=2>]
Thu, 29 Jan 2009 13:28:07 GMT ~ info ~ getting comment with .first
Thu, 29 Jan 2009 13:28:07 GMT ~ debug ~ (0.000000) SELECT "id", "name", "post_id" FROM "comments" WHERE ("name" = 'foo') AND ("post_id" IN (2)) ORDER BY "id" LIMIT 1
Thu, 29 Jan 2009 13:28:07 GMT ~ info ~ got comment #<Comment id=2 name="foo" post_id=2>
Thu, 29 Jan 2009 13:28:07 GMT ~ info ~ fetching post by association
Thu, 29 Jan 2009 13:28:07 GMT ~ info ~ getting comments with .all
Thu, 29 Jan 2009 13:28:07 GMT ~ debug ~ (0.000000) SELECT "id", "name", "post_id" FROM "comments" WHERE ("name" = 'foo') AND ("post_id" IN (1, 2)) ORDER BY "id"
Thu, 29 Jan 2009 13:28:07 GMT ~ info ~ got comments [#<Comment id=2 name="foo" post_id=2>]
Thu, 29 Jan 2009 13:28:07 GMT ~ info ~ getting comment with .first
Thu, 29 Jan 2009 13:28:07 GMT ~ debug ~ (0.000000) SELECT "id", "name", "post_id" FROM "comments" WHERE ("name" = 'foo') AND ("post_id" IN (1, 2)) ORDER BY "id" LIMIT 1
Thu, 29 Jan 2009 13:28:07 GMT ~ info ~ got comment #<Comment id=1 name="foo" post_id=1>