Skip to content

Instantly share code, notes, and snippets.

@matthandlersux
Created June 9, 2011 16:57
Show Gist options
  • Save matthandlersux/1017176 to your computer and use it in GitHub Desktop.
Save matthandlersux/1017176 to your computer and use it in GitHub Desktop.
DataMapper gist showing failing query on many to many association
require 'rubygems'
require 'datamapper'
DataMapper::Logger.new($stdout, :debug)
DataMapper.setup(:default, ENV['DATABASE_URL'] || 'sqlite3::memory:')
class Item
include DataMapper::Resource
property :id, Serial
property :name, String
has n, :tags, :through => Resource
end
class Tag
include DataMapper::Resource
property :id, Serial
property :name, String
has n, :items, :through => Resource
end
DataMapper.auto_migrate!
item = Item.create(:name => "thing")
t1 = Tag.create(:name => "tag1")
t2 = Tag.create(:name => "tag2")
t3 = Tag.create(:name => "tag3")
item.tags << t3
item.tags << t2
item.save
# Item.all(:tags => {:id => 2}) returns empty set
# The query it runs is all wrong:
#
# SELECT "id", "name" FROM "items" WHERE "id" IN (SELECT "tags"."id" FROM "tags" INNER JOIN "item_tags" ON "tags"."id" = "item_tags"."tag_id" INNER JOIN "items" ON "item_tags"."item_id" = "items"."id" WHERE "tags"."id" = 2) ORDER BY "id"
#
# as you can see, it looks for items.id in a query that returns a list of tags.id
# Item.all(Item.tags.id => 2) gets the result
# Query:
#
# SELECT "items"."id", "items"."name" FROM "items" INNER JOIN "item_tags" ON "items"."id" = "item_tags"."item_id" INNER JOIN "tags" ON "item_tags"."tag_id" = "tags"."id" WHERE "tags"."id" = 2 GROUP BY "items"."id", "items"."name" ORDER BY "items"."id"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment