The way I have implemented it, it would appear not.
In data_mapper terms - I have a join resource Entry, that joins Driver, Team and Season
I have a join resource Race that joins Track and Season
I have a join resource Placing that joins Entry and Race.
When I try to do something like "Show me all of the races that a particular entry has taken part in with something like
@entry_a.races.all
I get this error
DataObjects::SyntaxError: no such column: INNER (code: 1, sql state: , query: SELECT "races"."track_id", "races"."season _id", "races"."race_no" FROM "races" INNER JOIN "placings" ON INNER JOIN "entries" ON "placings"."entry_team_id" = "ent ries"."team_id" AND "placings"."entry_driver_id" = "entries"."driver_id" WHERE ("placings"."entry_team_id" = 'FER' AND " placings"."entry_driver_id" = 'MAS') GROUP BY "races"."track_id", "races"."season_id", "races"."race_no" ORDER BY "races "."track_id", "races"."season_id", uri: sqlite3:C/devProjects/formula1../spec/resources/test.db?scheme=sqlite3&user=&pas sword=&host=C&port=&query=&fragment=&adapter=sqlite3&path=/devProjects/formula1../spec/resources/test.db)
Its pretty easy to see what has happened here, the query wrapped by the .all method isn't expecting a join on join.
I managed to workaround this by writing some custom sql. While not ideal, it does do the job. It doesn't seem to be the ruby way though. Maybe my db schema sucks?
Here are my models (sorry about the code dump. I have gotten burned by a fellow SO who decided to downvote me because I hadn't quoted complete code)
require "rubygems"
require "sqlite3"
require "data_mapper"
require "bigdecimal"
#note that Dir.pwd refers to the location of the file that calls
if ENV["run_mode"] == "prod"
DataMapper::setup(:default, "sqlite3://#{Dir.pwd}/data/prod.db")
else
DataMapper::setup(:default, "sqlite3://#{Dir.pwd}../spec/resources/test.db")
end
class Entry
include DataMapper::Resource
belongs_to :team, :key => true
belongs_to :driver, :key => true
belongs_to :season, :key => true
has n, :placings
has n, :races, :through => :placings
def find_races
return repository.adapter.select('SELECT races.track_id, races.season_id, races.race_no FROM races INNER JOIN placings ON races.track_id = placings.race_track_id INNER JOIN entries ON placings.entry_driver_id = entries.driver_id WHERE (entries.team_id = ? AND entries.driver_id = ?)', self[:team_id], self[:driver_id])
end
end
class Track
include DataMapper::Resource
property :id, String, :key => true
has n, :races
end
class Race
include DataMapper::Resource
property :race_no, Integer
belongs_to :track, :key => true
belongs_to :season, :key => true
has n, :placings
has n, :entries, :through => :placings
end
class Placing
include DataMapper::Resource
property :id, Serial #put this in because dm was complaining that foreign keys from entry object were not unique
belongs_to :race
belongs_to :entry
end
class Season
include DataMapper::Resource
property :id, Integer, :key => true
has n, :races
has n, :entries
end
class Driver
include DataMapper::Resource
property :id, String, :key => true
property :team, String
property :ptd, Integer
property :races, Integer
property :grid, Object
property :race, Object
property :cumulativePoints, Object #the number of points accumulated at a particular point in the season
property :hcScore, Integer
property :domScore, Integer
property :nation, String
property :wins, Integer
property :podiums, Integer
has n, :entries
has n, :teams, :through => :entries
end
class Team
include DataMapper::Resource
property :id, String, :key => true
property :name, String
property :ptd, Integer
property :domScore, Integer
property :nation, String
has n, :entries
has n, :drivers, :through => :entries
def initialize(team_id)
self[:id] = team_id
self.save!
end
def add_driver(driver_id)
@driver = Driver.create(:id => driver_id)
Entry.create(:driver => (Driver.get driver_id), :team => self, :season => Season.last)
return @driver
end
end
class Nation
include DataMapper::Resource
property :id, String, :key => true
property :ptd, Integer
# could possibly have the drivers, teams and engines here as well
end
if ENV["run_mode"] == "test"
DataMapper.finalize.auto_migrate!
else
DataMapper.finalize.auto_upgrade!
end
After some testing, I think that the error you're seeing is related to having composite keys; try changing for a single Serial key for Race. I think it's a bug in DM.
I had the same error appear when trying to do Driver.first.races with composite keys, but with just a Serial key it worked:
$KCODE='u'
require 'rubygems'
require 'dm-core'
require 'dm-migrations'
DataMapper::Logger.new($stdout, :debug)
DataMapper.setup(:default, "sqlite::memory:")
class Entry
include DataMapper::Resource
#property :id, Serial # maybe also here? Although everything seems to work without
property :grid, Integer
property :position, Integer
belongs_to :driver, :key => true
belongs_to :race, :key => true
end
class Driver
include DataMapper::Resource
property :name, String, :key => true
has n, :entries
has n, :races, :through => :entries
def podiums; entries(:position => (1..3)) end
def wins; entries(:position => 1) end
end
class Race
include DataMapper::Resource
property :id, Serial
belongs_to :track
belongs_to :season
has n, :entries
has n, :drivers, :through => :entries
end
class Season
include DataMapper::Resource
property :year, Integer, :key => true
has n, :races
end
class Track
include DataMapper::Resource
property :name, String, :key => true
has n, :races
end
DataMapper.finalize.auto_migrate!
Entry.create(
:driver => Driver.new(:name => "Kimi Räikkönen"),
:grid => 7, :position => 1,
:race => Race.new(
:track => Track.new(:name => "Albert Park"),
:season => Season.new(:year => 2013)))