I have the following setup of models:
User
- has_many File (for userpics)
Gallery
- has_many File (for images)
Page
- has_one File (for background image)
Page object could share a File object with one or more Gallery objects for its background. And at some stage later a new model like
Product
- has_many File
or similar can appear in the App.
Note that i need a File model instead of just storing paths to the actual files because the File model can reference, in fact, several files in the filesystem
File
- id
- path
- path_poster
- path_m4v (HTML5 videos need up to three files for compatibility)
- path_webm
- path_ogv
- width
- height
- poster_width
- poster_height
- type
- ... etc....
So, is there a simple way (without overwriting the whole ORM class) to implement relationships that would use a "generic" pivot table with the following fields:
model_name VARCHAR(8) (or model_type_id TINYINT for speed)
model_id INT
file_id INT
relation_name VARCHAR(8) (e.g., Page model can have "background" and "logo" relation)
position INT
The reason: i want to have a universal App module for checking for "orphaned" files, and also be able to tell to what each file is attached, so, for example, when deleting a file from the Gallery the App would warn that the file is still attached to a Page as background.
Short answer is no. The reason is because model_id would conflict with other model IDs. If you wanted, you could do a unique index on model_name+model_id, but to join those two columns would require that you rewrite the ORM methods to join models together via their relationships.
I would honestly stick with simple pivot tables.