Hi.
Posted this to the DBIx::Class maillist, but not getting an traction there.
Perhaps someone else has run into this issue on a Catalyst app.
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: