dm_so_question_5638840.rb

#!/usr/bin/env ruby
#
# encoding: utf-8

require 'dm-migrations'

DataMapper::Logger.new $stdout, :debug
DataMapper.setup :default, "sqlite::memory:"

class A
  include DataMapper::Resource

  property :id, Serial

  has n, :bs
  has n, :cs, :through => :bs, :via => :c

  def active_c
    cs.active
  end
end

class B
  include DataMapper::Resource

  property :id, Serial

  belongs_to :a
  belongs_to :c
end

class C
  include DataMapper::Resource

  property :id, Serial
  property :end_on, Date
  property :applied_to, Date

  has n, :bs

  def self.active
    all(self.end_on => nil) + all(:conditions => [ "cs.end_on > applied_to" ])
  end
end

DataMapper.finalize
DataMapper.auto_migrate!

a = A.create
b = B.create(:c => C.create, :a => a)

puts a.active_c.inspect

output.txt

 ~ (0.000063) SELECT sqlite_version(*)
 ~ (0.000083) DROP TABLE IF EXISTS "as"
 ~ (0.000012) PRAGMA table_info("as")
 ~ (0.000202) CREATE TABLE "as" ("id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT)
 ~ (0.000013) DROP TABLE IF EXISTS "bs"
 ~ (0.000007) PRAGMA table_info("bs")
 ~ (0.000079) CREATE TABLE "bs" ("id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "a_id" INTEGER NOT NULL, "c_id" INTEGER NOT NULL)
 ~ (0.000076) CREATE INDEX "index_bs_a" ON "bs" ("a_id")
 ~ (0.000067) CREATE INDEX "index_bs_c" ON "bs" ("c_id")
 ~ (0.000012) DROP TABLE IF EXISTS "cs"
 ~ (0.000007) PRAGMA table_info("cs")
 ~ (0.000078) CREATE TABLE "cs" ("id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "end_on" DATE, "applied_to" DATE)
 ~ (0.000024) INSERT INTO "as" DEFAULT VALUES
 ~ (0.000026) INSERT INTO "cs" DEFAULT VALUES
 ~ (0.000035) INSERT INTO "bs" ("a_id", "c_id") VALUES (1, 1)
 ~ (0.000081) SELECT "id", "end_on", "applied_to" FROM "cs" WHERE ("id" IN (SELECT "cs"."id" FROM "cs" INNER JOIN "bs" ON "cs"."id" = "bs"."c_id" INNER JOIN "as" ON "bs"."a_id" = "as"."id" WHERE ("bs"."a_id" = 1 AND "cs"."end_on" IS NULL)) OR "id" IN (SELECT "cs"."id" FROM "cs" INNER JOIN "bs" ON "cs"."id" = "bs"."c_id" INNER JOIN "as" ON "bs"."a_id" = "as"."id" WHERE ("bs"."a_id" = 1 AND (cs.end_on > applied_to)))) GROUP BY "id", "end_on", "applied_to" ORDER BY "id"
[#<C @id=1 @end_on=nil @applied_to=nil>]