[Dbix-class] many_to_many with composite primary keys

Alejandro Imass aimass at yabarana.com
Thu Aug 23 17:56:46 GMT 2012


On Thu, Aug 23, 2012 at 1:31 PM, bill hauck <wbhauck at yahoo.com> wrote:
>
> Hi.
>
> I got it working.
>
> Is there a wiki for DBIx::Class that I can add a simple but functional
> example to so others might not struggle with it?  I'll add it to the
> Catalyst wiki in the mean time.
>

Consider Perlmonks as well!

-- 
Alejandro Imass

> Cheers,
>
> bill
>
>
> ________________________________
> From: bill hauck <wbhauck at yahoo.com>
> To: DBIx::Class user and developer list <dbix-class at lists.scsys.co.uk>
> Sent: Thursday, August 16, 2012 10:37 PM
> Subject: [Dbix-class] many_to_many with composite primary keys
>
>
>
> 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
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>
>
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk



More information about the DBIx-Class mailing list