datamapper_join.rb

#!/usr/bin/env ruby
require 'dm-core'
require 'pp'

DataMapper::Logger.new($stderr, :debug)
DataMapper.setup(:default, adapter: :postgres, database: 'dmbug')

class User
  include DataMapper::Resource
  property :id, Serial
  has n, :transfers

  #--
  # Can this be done with has n, with scope?
  def items
    Item.all(Item.transfer.user.id => id)
  end
end

class Project
  include DataMapper::Resource
  property :id,    Serial
  property :shiny, Boolean, required: true, default: false

  has n, :items
end

class Transfer
  include DataMapper::Resource
  property :id,      Serial
  property :user_id, Integer, required: true

  belongs_to :user
end

class Item
  include DataMapper::Resource
  property :id,          Serial
  property :project_id,  Integer, required: true
  property :transfer_id, Integer, required: true

  belongs_to :project
  belongs_to :transfer

  #--
  # Can this be done with has n, with scope?
  def self.shiny
    all(project.shiny => true)
  end
end

DataMapper.auto_migrate!
user     = User.create
project  = Project.create(shiny: true)
transfer = Transfer.create(user: user)
item     = Item.create(transfer: transfer, project: project)

# Geta all items for user where the project is shiny.

=begin
The following works and generates this SQL:

SELECT "items"."id", "items"."project_id", "items"."transfer_id" FROM "items" INNER JOIN "transfers" ON "items"."transfer_id" = "transfers"."id" INNER JOIN "users" ON "transfers"."user_id" = "users"."id" INNER JOIN "projects" ON "items"."project_id" = "projects"."id" WHERE ("users"."id" = 1 AND "projects"."shiny" = TRUE) GROUP BY "items"."id", "items"."project_id", "items"."transfer_id" ORDER BY "items"."id"
=end
Item.all(Item.transfer.user.id => user.id).all(Item.project.shiny => true).each do |shiny_item|
  pp shiny_item
end

=begin
The following looks identical to me (except each all() statement is inside a method) but generates invalid SQL (two joins on transfer):

SELECT "items"."id", "items"."project_id", "items"."transfer_id" FROM "items" INNER JOIN "transfers" ON "items"."transfer_id" = "transfers"."id" INNER JOIN "users" ON "transfers"."user_id" = "users"."id" INNER JOIN "transfers" ON "items"."transfer_id" = "transfers"."id" INNER JOIN "projects" ON "items"."project_id" = "projects"."id" WHERE ("users"."id" = 1 AND "projects"."shiny" = TRUE) GROUP BY "items"."id", "items"."project_id", "items"."transfer_id" ORDER BY "items"."id"
=end
user.items.shiny.each do |shiny_item|
  pp shiny_item
end