sql_debug.txt

Tue, 14 Feb 2012 14:56:18 GMT ~ debug ~ (0.002120) DROP TABLE IF EXISTS "users"
Tue, 14 Feb 2012 14:56:18 GMT ~ debug ~ (0.001219) DROP TABLE IF EXISTS "addresses"
Tue, 14 Feb 2012 14:56:18 GMT ~ debug ~ (0.003135) CREATE TABLE "users" ( "id" SERIAL NOT NULL PRIMARY KEY, "username" VARCHAR(50) NOT NULL, "age" SMALLINT NOT NULL )
Tue, 14 Feb 2012 14:56:18 GMT ~ debug ~ (0.002443) CREATE TABLE "addresses" ( "id" SERIAL NOT NULL PRIMARY KEY, "user_id" INTEGER NOT NULL, "street" VARCHAR(50) NOT NULL, "zipcode" VARCHAR(10) NOT NULL, "city" VARCHAR(50) NOT NULL )
Tue, 14 Feb 2012 14:56:18 GMT ~ debug ~ (0.000816) INSERT INTO "users" ("id", "username", "age") VALUES (1, 'John', 18)
Tue, 14 Feb 2012 14:56:18 GMT ~ debug ~ (0.000443) INSERT INTO "users" ("id", "username", "age") VALUES (2, 'Jane', 21)
Tue, 14 Feb 2012 14:56:18 GMT ~ debug ~ (0.000532) INSERT INTO "addresses" ("id", "user_id", "street", "zipcode", "city") VALUES (1, 1, 'Street 1/2', 'Chicago', '12345')
Tue, 14 Feb 2012 14:56:18 GMT ~ debug ~ (0.000463) INSERT INTO "addresses" ("id", "user_id", "street", "zipcode", "city") VALUES (2, 2, 'Street 2/4', 'Boston', '67890')
Tue, 14 Feb 2012 14:56:18 GMT ~ debug ~ (0.001570) SELECT "age", "username", "id", "street", "zipcode", "user_id", "city" FROM (SELECT * FROM "users" AS "left" NATURAL JOIN (SELECT * FROM "addresses" WHERE "city" = 'Boston') AS "right") AS "users_addresses" WHERE "city" = 'Boston'

veritas_join_and_restrict_spec.rb

require 'spec_helper'

describe 'join and restrict' do
  before(:all) do
    setup_db

    insert_user 1, 'John', 18
    insert_user 2, 'Jane', 21

    insert_address 1, 1, 'Street 1/2', 'Chicago', '12345'
    insert_address 2, 2, 'Street 2/4', 'Boston',  '67890'

    DataMapper.relation_registry << Veritas::Relation::Gateway.new(
      DATABASE_ADAPTER, Address::Mapper.base_relation)

    DataMapper.relation_registry << Veritas::Relation::Gateway.new(
      DATABASE_ADAPTER, User::Mapper.base_relation)
  end

  let(:operation) do
    left  = DataMapper.relation_registry[:users]
    right = DataMapper.relation_registry[:addresses].restrict { |r| r.city.eq('Boston') }

    left.join(right)
  end

  class Address
    attr_reader :id, :street, :zipcode, :city

    def initialize(attributes)
      @id, @street, @zipcode, @city = attributes.values_at(:id, :name, :zipcode, :city)
    end

    class Mapper < DataMapper::Mapper::VeritasMapper
      map :id,      :type => Integer
      map :user_id, :type => Integer
      map :street,  :type => String
      map :zipcode, :type => String
      map :city,    :type => String

      model Address
      name :addresses
    end
  end

  class User
    attr_reader :id, :name, :age

    def initialize(attributes)
      @id, @name, @age = attributes.values_at(:id, :name, :age)
    end

    class Mapper < DataMapper::Mapper::VeritasMapper
      map :id, :type => Integer
      map :name, :to => :username, :type => String
      map :age, :type => Integer

      model User
      name :users
    end
  end

  it 'finds user with a specific address' do
    users = User::Mapper.new(operation.restrict { |r| r.city.eq('Boston') }.optimize).to_a
    user  = users.first

    users.should have(1).item

    user.should be_instance_of(User)
    user.name.should eql('Jane')
  end

end

One thing I can say is the query looks a bit weird. I would've expected the restriction to get "pushed down" to the left and right side as a general optimization. Later on a DB optimizer might pull those up, but for the general case I would want the left and right side to have their own restriction, so that if those were relations on different datastores they would return the smallest set of results possible, so the in-memory ops are minimized.

@dkubb I just pushed this spec to dm-mapper