CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
Fabian Schwarz
Fabian Schwarz

Reputation: 33

Associate Active Record tables through non-primary, non-unique column in Rails

I have a table "films" whose schema looks like this:

create_table "films", force: :cascade do |t|
    t.integer "tmdb_id", null: false
    t.string "language_iso_639_1", null: false
    t.string "title", null: false
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["tmdb_id", "language_iso_639_1"], name: "index_films_on_tmdb_id_and_language_iso_639_1", unique: true
end

As you can see the tmdb_id itself is not unique, only the combination of the tmdb_id and the language_iso_639_1 together. So far this is working as expected and lets me create records like these:

[#<Film:0x0000000108156630                                      
  id: 1,                                                        
  tmdb_id: 12,                                                  
  language_iso_639_1: "en",                                     
  title: "Finding Nemo",                                        
  created_at: Fri, 15 Apr 2022 14:58:43.128785000 UTC +00:00,   
  updated_at: Fri, 15 Apr 2022 14:58:43.128785000 UTC +00:00>,  
 #<Film:0x00000001081564f0                                      
  id: 2,                                                        
  tmdb_id: 12,                                                  
  language_iso_639_1: "de",                                     
  title: "Findet Nemo",                                         
  created_at: Fri, 15 Apr 2022 14:58:52.563142000 UTC +00:00,   
  updated_at: Fri, 15 Apr 2022 14:58:52.563142000 UTC +00:00>,
 #<Film:0x0000000108156428
  id: 3,
  tmdb_id: 12,
  language_iso_639_1: "fr",
  title: "Le Monde de Nemo",
  created_at: Fri, 15 Apr 2022 14:59:03.318667000 UTC +00:00,
  updated_at: Fri, 15 Apr 2022 14:59:03.318667000 UTC +00:00>]

Now I want to create another table "film_backdrops" to save backdrop-images for the specific films. Those images do not differ for different languages, so only the tmdb_id is important.

My (probably to be revised) scheme for this table currently looks like this:

create_table "film_backdrops", force: :cascade do |t|
    t.integer "tmdb_id", null: false
    t.string "file_path", null: false
    t.float "vote_average"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
 end

And an entry would look like this, for example:

#<FilmBackdrop:0x0000000108a3e798                                       
 id: 1,                                                                 
 tmdb_id: 12,                                                           
 file_path: "/bla/bla/image.png",                                       
 vote_average: 9.8,                                                     
 created_at: Fri, 15 Apr 2022 15:00:59.635324000 UTC +00:00,            
 updated_at: Fri, 15 Apr 2022 15:00:59.635324000 UTC +00:00>

I want to link these two tables so that the same FilmBackdrop records are returned for films with the same tmdb_id and irrespective of the language_iso_639_1 field, like this:

Film.find_by(tmdb_id: 12, language_iso_639_1: "en").film_backdrops # => #<FilmBackdrop:0x0000000107b445d0
Film.find(tmdb_id: 12, language_iso_639_1: "de").film_backdrops # => #<FilmBackdrop:0x0000000107b445d0
Film.find(tmdb_id: 12, language_iso_639_1: "fr").film_backdrops # => #<FilmBackdrop:0x0000000107b445d0

The other way around should also work:

FilmBackdrop.find(1).films # => #<Film:0x00000001077cd348, #<Film:0x00000001077cd280 , #<Film:0x00000001077cd1b8

Since I don't need the primary key "id" of Film for this and "tmdb_id" of Film alone is not unique either, no approach I have tried so far has been successful.

Is it even possible to get it done without creating a third "inbetween" table?

Upvotes: 0

Views: 96

Answers (3)

max
max

Reputation: 101821

What I'm guess that your doing here is localizing films and this solution feels very backwards and overcomplicated.

Rails doesn't really do composite primary keys / foreign keys as thats not something thats widely supported among databases. There is a gem for composite primary keys but it is built on hacking ActiveRecord internals so I would consider if this is something you really want to inflict on your app.

I would instead do it like so:

class Film
  has_many :film_localizations
  has_many :film_backdrops
end

# rails g model film_localization film:belongs_to language_iso_639_1:string title:string
class FilmLocalization
  belongs_to :film
end

If you want to use an externally derived UUID instead of an auto-incrementing integer or generated UUID for the films table thats perfectly fine.

But your models should have a single unique primary key which other tables can use to refer to it or you're going to have a very difficult time as you're fighting the entire ORM / conventions.

Delegation can be used if you want a FilmLocalization to behave like a Film.

There are also ready made solutions for translating model data such as Mobility.

Upvotes: 0

Tony Arra
Tony Arra

Reputation: 11099

Assuming no changes to your provided schema, the only thing you need to do is specify the foreign_key and primary_key options on your associations:

class Film
  has_many :film_backdrops, foreign_key: :tmdb_id, primary_key: :tmdb_id
end

class FilmBackdrop
  has_many :films, foreign_key: :tmdb_id, primary_key: :tmdb_id
end

Note that while this will work for your purposes, it's non-standard in Rails. So some functionality, like the inverse associations, will not be supported.

Upvotes: 0

David Aldridge
David Aldridge

Reputation: 52336

It sounds like you're missing a model in which the value of tmdb_id is a unique key.

This would have a has_many association to Film, and, I think, has_many to FilmBackdrop.

Then Film and FilmBackdrop would each has_many of the other through this model.

Upvotes: 1

Related Questions