#1467 confirmed
Postmodern

multiple query paths map to the first child model.

Reported by Postmodern | January 4th, 2011 @ 08:30 PM

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>'

Comments and changes to this ticket

  • Postmodern

    Postmodern January 4th, 2011 @ 08:39 PM

    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}
)
  • Jordan Ritter

    Jordan Ritter January 4th, 2011 @ 09:02 PM

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

  • Jordan Ritter

    Jordan Ritter January 4th, 2011 @ 09:03 PM

    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.

  • Postmodern

    Postmodern January 4th, 2011 @ 09:08 PM

    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)
  • Jordan Ritter

    Jordan Ritter January 6th, 2011 @ 01:50 PM

    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.

  • Martin Gamsjaeger (snusnu)

    Martin Gamsjaeger (snusnu) January 14th, 2011 @ 07:23 PM

    • State changed from “new” to “confirmed”

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

People watching this ticket

Tags

Pages