How do I make a DBIx::Class relationship with a fixed join condition?

Question

We have a link table that can handle multiple types of object on one side, and I can't work out how to get from one of these objects to the link table using has_many.

Example: link table contains:

id link_id link_table resource_id
1  1       page       3
2  1       page       5
3  2       page       3
4  1       not_page   1

Building the relationship from the resource side is easy enough:

Resource->has_many(links => 'Link', 'resource_id');

but I haven't been able to get the corresponding relationship from the page side:

Page->has_many(links => 'Link', 'link_id');

would get the not_page link

Page->has_many(links => 'Link', {'foreign.link_id' => 'self.id', 'foreign.link_table' => 'page'});

gives an 'Invalid rel cond val page' error (which was not that surprising to me).

Page->has_many(links => 'Link', {'foreign.link_id' => 'self.id', 'foreign.link_table' => '"page"'});

gives an 'Invalid rel cond val "page"' error. Throwing backslashes in didn't help.

DBIx::Class::Relationship::Base says:

The condition needs to be an SQL::Abstract-style representation of the join between the tables

and I have tried various different options from there, such as:

Page->has_many(links => 'Link', {'foreign.link_id' => 'self.id', 'foreign.link_table' => {'=', 'page'}});

but without any success at all.

If I added another field to the page table which always contains the value 'page' I could do

Page->has_many(links => 'Link', {'foreign.link_id' => 'self.id', 'foreign.link_table' => 'self.what_table_am_i'});

but that's hardly an optimal solution.

Splitting the link table into a separate one for each type may be a possibility, but this is an existing project that is being considered for adaptation to DBIx::Class, and there may be other places where splitting a table into multiple other tables is more hassle than it's worth.

Zink

Do you want "belongs_to" on the other side of "has_many"? – Elmira

The has_many was more important, so I elected not to add more complications to the question than necessary. I hope Brian's answer is able to be modified for the belongs_to case. – Zink

Answer 1

You should just make a wrapper method that calls the relationship with the required arguments:

Page->has_many(__all_links => 'Link', 'link_id');

sub links {
    shift->__all_links({link_table => 'page'});
}

This would be pretty easy to turn into a DBIx::Class component if you have multiple tables that need to have this kind of join logic.

Mabel

And how then this may be used with search()? I tried: $c->model('Page')->search(undef, {prefetch => "links"})->all; But it says: DBIx::Class::ResultSet::all(): No such relationship links on Page – Application

Answer 2

It can be specified in the has_many call like so:

Page->has_many(links => 'Link', 'link_id',
                    { where => { link_table => 'page'} });

See: DBIx::Class Cookbook

Thegn