[Dbix-class] many_to_many with composite primary keys

[bill hauck]

Hi.

Please excuse me if this has been asked and answered before.  I didn't see it in the maillist archive so here goes.

I need to connect two tables through a relation table for a many_to_many.  The issue I'm running into is that one of the tables has a composite primary key.  I'm using this in a Catalyst application through a Template Toolkit template.

So, before going through the whole thing, is it possible to have a composite primary key used in many to many?  If not, any suggestions on a workaround?

If it is possible, here's my setup.  The user will submit issues into complexity_submission.  A trigger will create a duplicate entry in table complexity giving the unique post id and then a version of the post.

Tables, trigger, Schema::Result relations listed, and template below.

Thanks in advance,

bill

Version Info:
Catalyst 5.80029

DBIx::Class $VERSION = '0.08124';
perl 5, version 12, subversion 2 (v5.12.2) built for i686-linux


mysql> describe complexity_submission;

+---------------+------------------+------+-----+-------------------+-----------------------------+
| Field         | Type             | Null | Key | Default           | Extra                       |
+---------------+------------------+------+-----+-------------------+-----------------------------+
| id            | int(10) unsigned | NO   | PRI | NULL              | auto_increment              |
| creator_email | varchar(255)     | YES  |     | NULL              |                             |
| opt_in        | int(1) unsigned  | NO   |     | 0                 |                             |
| recorded      | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| body          | text             | YES  |     | NULL              |                             |
+---------------+------------------+------+-----+-------------------+-----------------------------+
5 rows in set (0.01 sec)

mysql> describe complexity;
+----------------+------------------+------+-----+-------------------+-----------------------------+
| Field          | Type             | Null | Key | Default           | Extra                       |
+----------------+------------------+------+-----+-------------------+-----------------------------+
| post_id        | int(10) unsigned | NO   | PRI | 0                 |                             |
| version        | int(10) unsigned | NO   | PRI | NULL              | auto_increment              |
| published      | tinyint(1)       | YES  |     | 0                 |                             |
| curator_status | int(1)           | NO   |     | 0                 |                             |
| prc_status     | int(1)           | NO   |     | 0                 |                             |
| creator_email  | varchar(255)     | YES  |     | NULL              |                             |
| opt_in         | int(1) unsigned  | NO   |     | 0                 |                             |
| recorded       | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| body           | text             | YES  |     | NULL              |                             |
| prc_note       | text             | YES  |     | NULL              |                             |
+----------------+------------------+------+-----+-------------------+-----------------------------+
10 rows in set (0.00 sec)

mysql> describe tag;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name        | varchar(255)     | YES  |     | NULL    |                |
| description | text             | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> describe complexity_tag;
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| post_id | int(10) unsigned | NO   | PRI | NULL    |       |
| version | int(10) unsigned | NO   | PRI | NULL    |       |
| tag_id  | int(10) unsigned | NO   | PRI | NULL    |       |
+---------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: bi_complexity_submission_trigger
               Event: INSERT
               Table: complexity_submission
           Statement: begin
insert into complexity (post_id, creator_email, opt_in, recorded, body)
values
(new.id, new.creator_email, new.opt_in, new.recorded, new.body);
end
              Timing: AFTER
             Created: NULL
            sql_mode:
             Definer: momentum at localhost
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci


Schema::Result::Complexity.pm
__PACKAGE__->set_primary_key("post_id", "version");
...
__PACKAGE__->has_many(map_complexity_tag => 'Momentum::Schema::Result::ComplexityTag', ['post_id', 'version']);
__PACKAGE__->many_to_many(tags => 'map_complexity_tag', 'tag_id');


Schema::Result::Tag.pm
__PACKAGE__->set_primary_key("id");
...
__PACKAGE__->has_many(map_complexity_tag => 'Momentum::Schema::Result::ComplexityTag' => 'tag_id');
__PACKAGE__->many_to_many(complexities => 'map_complexity_tag', ['post_id','version']);


Schema::Result::ComplexityTag.pm
__PACKAGE__->set_primary_key("post_id", "version", "tag_id");
...
__PACKAGE__->belongs_to(complexity => 'Momentum::Schema::Result::Complexity', ['post_id', 'version']);
__PACKAGE__->belongs_to(tag => 'Momentum::Schema::Result::Tag', 'tag_id');


Complexity.pm controller ...
sub test : Path('test') : Args(0) {
    my ( $self, $c ) = @_;
    $c->stash->{complexity} =
      $c->model('MomentumDB::Complexity')->find ( { post_id => 7, version=>5 } );
}



complexity/test.tt
Tags Follow ... <br />
[% complexity.body %]
<br />
Tag name: [% complexity.tags.name %]
<br />


and the top part of the error I'm geting ...

Couldn't render template "complexity/test.tt: undef error - Can't handle condition post_id yet :( at /usr/local/lib/perl5/site_perl/5.12.2/DBIx/Class/ResultSource.pm line 1471.
 at /usr/local/lib/perl5/site_perl/5.12.2/DBIx/Class/Schema.pm line 1061
DBIx::Class::Schema::throw_exception('Momentum::Schema=HASH(0x9873d10)', 'Can\'t handle condition post_id yet :( at /usr/local/lib/perl...') called at /usr/local/lib/perl5/site_perl/5.12.2/DBIx/Class/Row.pm line 1441
DBIx::Class::Row::throw_exception('Momentum::Model::MomentumDB::Complexity=HASH(0x9d60c80)', 'Can\'t handle condition post_id yet :( at /usr/local/lib/perl...') called at /usr/local/lib/perl5/site_perl/5.12.2/DBIx/Class/Relationship/Base.pm line 271
DBIx::Class::Relationship::Base::__ANON__('Can\'t handle condition post_id yet :( at /usr/local/lib/perl...') called at /usr/local/lib/perl5/site_perl/5.12.2/Try/Tiny.pm line 100
Try::Tiny::try('CODE(0x9c52ec0)', 'Try::Tiny::Catch=REF(0x9c471d0)') called at /usr/local/lib/perl5/site_perl/5.12.2/DBIx/Class/Relationship/Base.pm line 275



Please let me know if you need more info.

Thanks again,

bill