[Dbix-class] Combining link tables

[Ash Berlin]

Will Hawes wrote:
> My database has a `file` table used to catalogue file meta info 
> (mimetype, filename, size, etc). Over time various other tables have 
> come to reference the file table (e.g. article, product, 
> product_category) and I now have several link tables: article_file, 
> product_file, product_category. I currently have things set up with DBIC 
> using has_many / many_to_many relationships in the normal way.
> 
> Since all these link tables have identical structure, with a column for 
> the article/product/product_category in question and a column for the 
> file object, I'm thinking it might make sense to merge them.
> 
> I'm thinking of using a table along the lines of
> 
> CREATE TABLE object_file(
>   `id` int(10) not null auto_increment primary key,
>   `table` char(50) not null,
>   `pk` int(10) not null,
>   `file` int(10) not null
> );
> 
> where `table` would store e.g. "article", "product", "product_category", 
> `pk` would store the primary key value of the 
> article/product/product_category in question and `file` the primary key 
> value of the file.
> 
> I would need to pass the table name of the 
> article/product/product_category as well as its pk value when searching 
> for related files, but I don't see a way to specify a relationship 
> condition containing a fixed value, only in the form "foreign.column => 
> self.column".
> 
> So, questions:
> 
> 1) Is this a really bad idea?

Yesyeysyes. Going from normalized to big pile o'mess. Please don't :)
> 
> 2) If not, can it be implemented with relationships, or would I need to 
> write custom accessors or use a custom resultset class to get the 
> desired behaviour?
> 

Tho just to show how great DBIx::Class is, I'll tell you how to do it 
anyway: See "Dynamic Sub-classing DBIx::Class proxy classes" in 
DBIx::Class::Manual::Cookbook

Ash