I’m currently working on a web interface for an application whose database I have no control over.
The database schema (shortened for brevity) is as follows:
CREATE TABLE songs(
songId INTEGER,
chartId INTEGER,
version INTEGER
);
CREATE TABLE scores(
musicId INTEGER,
level INTEGER,
version INTEGER
);
I’d like to set up a relationship like this:
class Song < ApplicationRecord
end
class Score < ApplicationRecord
# musicId => songId
# level => chartId
# version => version
# Association should be optional.
belongs_to :song
end
How would I best proceed in this situation?
I’ve tried hacking a solution using the scope parameter, but it is messy and it causes the N+1 problem, because I need to access a column in Song to calculate some statistics.
Heya beerpsi – thankfully Rails 7.1 has support for something called Composite Primary Keys which will allow you to use ActiveRecord to JOIN these two tables together. It can do this pretty easily, even though they have dissimilar key names.
An easy way to figure out the syntax for the necessary has_many and belongs_to entries for your models is by using The Brick gem. Here is a quick video demonstration of doing this:
From the video, you can see that this is how you would build out your models:
class Song < ApplicationRecord
# Primary key: songId, chartId, version
has_many :scores, query_constraints: ["musicId", "level", "version"]
end
class Score < ApplicationRecord
belongs_to :songs, query_constraints: ["musicId", "level", "version"]
end
Original SQL code to create your tables along with primary and foreign keys can be found here.
assuming Song#song_id and Score#music_id are primary keys
class Song < ApplicationRecord
self.primary_key = :song_id
has_many :scores, foreign_key: :music_id
end
class Score < ApplicationRecord
self.primary_key = :music_id
belongs_to :song, foreign_key: :music_id
end
and you can query them as: Song.includes(:scores).where(scores: {level: 5..})
Also, you can use composite primary keys as self.primary_key = [:music_id, :version] but you need to ensure that you’re on Rails 7.1+
Mentioned Brick gem seems to be nice option to generate models
