8

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.

cjm
  • 61,471
  • 9
  • 126
  • 175
Cebjyre
  • 6,552
  • 3
  • 32
  • 57
  • Do you want "belongs_to" on the other side of "has_many"? – jrockway Feb 23 '09 at 18:13
  • 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. – Cebjyre Feb 24 '09 at 00:23

2 Answers2

3

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.

Brian Phillips
  • 12,693
  • 3
  • 29
  • 26
  • 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 – Oleg G May 08 '16 at 07:26
1

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

a'r
  • 35,921
  • 7
  • 66
  • 67