Skip to content

Instantly share code, notes, and snippets.

@slaskis
Created November 15, 2010 21:32
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save slaskis/700986 to your computer and use it in GitHub Desktop.
Save slaskis/700986 to your computer and use it in GitHub Desktop.
DataMapper generates the wrong SQL query with some relationships.
require "rubygems"
require "datamapper"
class Person
include DataMapper::Resource
property :id, Serial
property :name, String, :required => true
property :role, String, :set => %w(boss rookie)
has n, :media, :through => Resource
end
class Medium
include DataMapper::Resource
property :id, Serial
property :title, String, :required => true
property :metadata, Yaml
has n, :people, :through => Resource
def self.filter( f )
unless f.to_s.strip.empty?
r = /^#{f.to_s.strip}/i
rejected = all.map { |m| m.id if (m.title =~ r).nil? && m.metadata && (m.metadata["Keywords"]||[]).grep(r).empty? }.compact
all(:id.not => rejected)
else
all
end
end
end
DataMapper.finalize
DataMapper::Logger.new(STDOUT,:debug)
DataMapper.setup(:default, "sqlite3::memory:")
DataMapper.auto_migrate!
a = Person.create(:name => "Alice", :role => "boss")
b = Person.create(:name => "Bob", :role => "rookie")
10.times { a.media.create( :title => "XYZ", :metadata => { "Keywords" => %w(one two three four five six)})}
10.times { b.media.create( :title => "ABC", :metadata => { "Keywords" => %w(seven eight nine ten eleven)})}
# Works
p Medium.filter("one")
# Strangely enough this one works (copied out of Medium#filter):
r = /^seven/i
ids = b.media.map { |m| m.id if (m.title =~ r).nil? && m.metadata && (m.metadata["Keywords"]||[]).grep(r).empty? }.compact
p b.media(:id.not => ids)
# Fails with SQL error: no such column: media.person_id (code: 1, sql state: , query: SELECT "media"."id", "media"."title" FROM "media" INNER JOIN "medium_people" ON "media"."id" = "medium_people"."medium_id" AND "media"."person_id" = 1 INNER JOIN "people" ON "medium_people"."person_id" = "people"."id" WHERE "medium_people"."person_id" = 1 GROUP BY "media"."id", "media"."title" ORDER BY "media"."id", uri: sqlite3://:memory:)
p a.media.filter("eight")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment