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: momentum@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
_______________________________________________
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/
