multiple query paths map to the first child model.

[solnic]

I noticed when using multiple query paths with a model that had multiple belongs_to relationships, the other query paths would map to the first child model.

gem 'dm-core', '~> 1.0.2'
gem 'dm-migrations', '~> 1.0.2'

require 'dm-core'
require 'dm-migrations'

class HostName

  include DataMapper::Resource
  include DataMapper::Migrations

  property :id, Serial

  property :name, String

  has 0..n, :urls

end

class Port

  include DataMapper::Resource
  include DataMapper::Migrations

  property :id, Serial

  property :number, Integer

  has 0..n, :urls

end

class Url

  include DataMapper::Resource
  include DataMapper::Migrations

  property :id, Serial

  belongs_to :host_name

  belongs_to :port

  property :path, String

end

DataMapper.setup(:default, 'sqlite3:dm_bug.db')
DataMapper.auto_migrate!

url = Url.create(
  :host_name => {:name => 'example.com'},
  :port => {:number => 80},
  :path => '/index.html'
)

# works
puts (
  Url.all('host_name.name' => 'example.com') &
  Url.all('port.number' => 80)
).first


# fails
puts Url.first(
  'host_name.name' => 'example.com',
  'port.number' => 80
)

#<Url:0x00000001c522d0>
/home/hal/.rvm/gems/ruby-1.9.2-p136/gems/dm-do-adapter-1.0.2/lib/dm-do-adapter/adapter.rb:142:in `execute_reader': no such column: host_names.port_id (DataObjects::SyntaxError)
    from /home/hal/.rvm/gems/ruby-1.9.2-p136/gems/dm-do-adapter-1.0.2/lib/dm-do-adapter/adapter.rb:142:in `block in read'
    from /home/hal/.rvm/gems/ruby-1.9.2-p136/gems/dm-do-adapter-1.0.2/lib/dm-do-adapter/adapter.rb:260:in `with_connection'
    from /home/hal/.rvm/gems/ruby-1.9.2-p136/gems/dm-do-adapter-1.0.2/lib/dm-do-adapter/adapter.rb:138:in `read'
    from /home/hal/.rvm/gems/ruby-1.9.2-p136/gems/dm-core-1.0.2/lib/dm-core/repository.rb:162:in `read'
    from /home/hal/.rvm/gems/ruby-1.9.2-p136/gems/dm-core-1.0.2/lib/dm-core/model.rb:379:in `first'
    from dm_bug.rb:64:in `<main>'

---

Created by Postmodern - 2011-01-05 02:30:47 UTC

Original Lighthouse ticket: http://datamapper.lighthouseapp.com/projects/20609/tickets/1467

[solnic]

Ah, as dkubb suggested using a Hash style of query seems to also work:

puts Url.first(
  :host_name => {:name => ’example.com’},
  :port => {:number => 80}
)

by Postmodern

[solnic]

I think this is ultimately the same problem as: https://gist.github.com/276940

by Jordan Ritter

[solnic]

Postmodern: can you turn on SQL logging? I’m curious whether the Hash style works because intermediate data sets are materialized, or if it results in a different (and more correct) SQL query being generated.

by Jordan Ritter

[solnic]

SQL log:

>>> Multi-query using & operator
 ~ (0.000230) SELECT "id", "path", "host_name_id", "port_id" FROM "urls" WHERE ("id" IN (SELECT "urls"."id" FROM "urls" INNER JOIN "host_names" ON "urls"."host_name_id" = "host_names"."id" WHERE "host_names"."name" = ’example.com’) AND "id" IN (SELECT "urls"."id" FROM "urls" INNER JOIN "ports" ON "urls"."port_id" = "ports"."id" WHERE "ports"."number" = 80)) GROUP BY "id", "path", "host_name_id", "port_id" ORDER BY "id" LIMIT 1
>>> Hash-style query
 ~ (0.000186) SELECT "id", "path", "host_name_id", "port_id" FROM "urls" WHERE ("host_name_id" IN (SELECT "id" FROM "host_names" WHERE "name" = ’example.com’) AND "port_id" IN (SELECT "id" FROM "ports" WHERE "number" = 80)) ORDER BY "id" LIMIT 1
>>> Failing query
 ~ (0.000120) SELECT "urls"."id", "urls"."path", "urls"."host_name_id", "urls"."port_id" FROM "urls" INNER JOIN "host_names" ON "urls"."host_name_id" = "host_names"."id" INNER JOIN "ports" ON "host_names"."port_id" = "ports"."id" WHERE ("host_names"."name" = ’example.com’ AND "ports"."number" = 80) GROUP BY "urls"."id", "urls"."path", "urls"."host_name_id", "urls"."port_id" ORDER BY "urls"."id" LIMIT 1
 ~ no such column: host_names.port_id (code: 1, sql state: , query: SELECT "urls"."id", "urls"."path", "urls"."host_name_id", "urls"."port_id" FROM "urls" INNER JOIN "host_names" ON "urls"."host_name_id" = "host_names"."id" INNER JOIN "ports" ON "host_names"."port_id" = "ports"."id" WHERE ("host_names"."name" = ’example.com’ AND "ports"."number" = 80) GROUP BY "urls"."id", "urls"."path", "urls"."host_name_id", "urls"."port_id" ORDER BY "urls"."id" LIMIT 1, uri: sqlite3://dm_bug.db)

by Postmodern

[solnic]

Interesting! So the nested hash stuff does work without materializing intermediate queries (using subselects instead)..

OK, I’m going to explore converting String-based query params into nested-hash and re-execing the Query transparently, as a short-term fix while fix_joins is being worked on.

by Jordan Ritter