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

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?

CodePudding user response：

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.

CodePudding user response：

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.

CodePudding user response：

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.

CodePudding user response：

Side note: Are you using active storage for the images? If not, I would recommend checking is out. It will help you a lot.

You dont need any extra tables. Maybe you could share your model definitions to shed some light on why it is not working. But there are many options to get your data, here is one example. Define a helper method on your "Film" model:

def relevant_backdrops
  FilmBackdrop.where(tmdb_id: tmdb_id)
end