How do I add relationships at runtime using DBIx::Class and Catalyst?

Question

In the application I am building, users can specify relationships between tables.

Since I only determine this at runtime, I can't specify has_many or belongs_to relationships in the schema modules for startup.

So given two tables; system and place, I would like to add the relationship to join records between them.

I have part of the solution below:

$rs = $c->model('DB::system')->result_source;
$rs->add_relationship('locations','DB::place',{'foreign.fk0' => 'self.id'});

So the column fk0 would be the foreign key mapping to the location primary key id.

I know there must be a re-registration to allow future access to the relationship but I can't figure it out.

Answer 1

I don't believe you can re-define these relationships after an application is already running. At least not without discarding any existing DBIC objects, and re-creating them all from scratch. At that point, it would be easier to just re-start your application, I suspect.

If you're content defining these things dynamically at compile time, that is possible... we do something similar in one of our applications.

If that would be useful to you, I can provide some sample code.

The DBIx::Class::ResultSet::View module might provide a rough approximation of what you're looking for, by letting you execute arbitrary code, but retrieving the results as DBIx objects.

My general opinion on things like this, is that any abstraction layer (and an ORM is an abstraction layer), is intended to make life easier. When it gets in the way of making your application do what it wants, it's no longer making life easier, and ought to be discarded (for that specific use--not necessarily for every use). For this reason, I would suggest using DBI, as you suggested in one of your comments. I suspect it will make your life much easier in this case.