[Dbix-class] many_to_many with composite primary keys
bill hauck
wbhauck at yahoo.com
Thu Aug 23 17:31:43 GMT 2012
Hi.
I got it working.
Is there a wiki for DBIx::Class that I can add a simple but functional exam=
ple to so others might not struggle with it? =A0I'll add it to the Catalyst=
wiki in the mean time.
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. =A0I didn't s=
ee it in the maillist archive so here goes.
>
>I need to connect two tables through a relation table for a many_to_many. =
=A0The issue I'm running into is that one of the tables has a composite pri=
mary key. =A0I'm using this in a Catalyst application through a Template To=
olkit template.
>
>So, before going through the whole thing, is it possible to have a composi=
te primary key used in many to many? =A0If not, any suggestions on a workar=
ound?
>
>If it is possible, here's my setup. =A0The user will submit issues into co=
mplexity_submission. =A0A trigger will create a duplicate entry in table co=
mplexity 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=A0$VERSION =3D '0.08124';
>perl 5, version 12, subversion 2 (v5.12.2) built for i686-linux
>
>
>mysql> describe complexity_submission;
>
>+---------------+------------------+------+-----+-------------------+-----=
------------------------+
>| Field =A0 =A0 =A0 =A0 | Type =A0 =A0 =A0 =A0 =A0 =A0 | Null | Key | Defa=
ult =A0 =A0 =A0 =A0 =A0 | Extra =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
|
>+---------------+------------------+------+-----+-------------------+-----=
------------------------+
>| id =A0 =A0 =A0 =A0 =A0 =A0| int(10) unsigned | NO =A0 | PRI | NULL =A0 =
=A0 =A0 =A0 =A0 =A0 =A0| auto_increment =A0 =A0 =A0 =A0 =A0 =A0 =A0|
>| creator_email | varchar(255) =A0 =A0 | YES =A0| =A0 =A0 | NULL =A0 =A0 =
=A0 =A0 =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 |
>| opt_in =A0 =A0 =A0 =A0| int(1) unsigned =A0| NO =A0 | =A0 =A0 | 0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 |
>| recorded =A0 =A0 =A0| timestamp =A0 =A0 =A0 =A0| NO =A0 | =A0 =A0 | CURR=
ENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
>| body =A0 =A0 =A0 =A0 =A0| text =A0 =A0 =A0 =A0 =A0 =A0 | YES =A0| =A0 =
=A0 | NULL =A0 =A0 =A0 =A0 =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
=A0 =A0 =A0 =A0 =A0 |
>+---------------+------------------+------+-----+-------------------+-----=
------------------------+
>5 rows in set (0.01 sec)
>
>mysql> describe complexity;
>+----------------+------------------+------+-----+-------------------+----=
-------------------------+
>| Field =A0 =A0 =A0 =A0 =A0| Type =A0 =A0 =A0 =A0 =A0 =A0 | Null | Key | D=
efault =A0 =A0 =A0 =A0 =A0 | Extra =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 |
>+----------------+------------------+------+-----+-------------------+----=
-------------------------+
>| post_id =A0 =A0 =A0 =A0| int(10) unsigned | NO =A0 | PRI | 0 =A0 =A0 =A0=
=A0 =A0 =A0 =A0 =A0 | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 |
>| version =A0 =A0 =A0 =A0| int(10) unsigned | NO =A0 | PRI | NULL =A0 =A0 =
=A0 =A0 =A0 =A0 =A0| auto_increment =A0 =A0 =A0 =A0 =A0 =A0 =A0|
>| published =A0 =A0 =A0| tinyint(1) =A0 =A0 =A0 | YES =A0| =A0 =A0 | 0 =A0=
=A0 =A0 =A0 =A0 =A0 =A0 =A0 | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 |
>| curator_status | int(1) =A0 =A0 =A0 =A0 =A0 | NO =A0 | =A0 =A0 | 0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 |
>| prc_status =A0 =A0 | int(1) =A0 =A0 =A0 =A0 =A0 | NO =A0 | =A0 =A0 | 0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 |
>| creator_email =A0| varchar(255) =A0 =A0 | YES =A0| =A0 =A0 | NULL =A0 =
=A0 =A0 =A0 =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 |
>| opt_in =A0 =A0 =A0 =A0 | int(1) unsigned =A0| NO =A0 | =A0 =A0 | 0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 |
>| recorded =A0 =A0 =A0 | timestamp =A0 =A0 =A0 =A0| NO =A0 | =A0 =A0 | CUR=
RENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
>| body =A0 =A0 =A0 =A0 =A0 | text =A0 =A0 =A0 =A0 =A0 =A0 | YES =A0| =A0 =
=A0 | NULL =A0 =A0 =A0 =A0 =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
=A0 =A0 =A0 =A0 =A0 |
>| prc_note =A0 =A0 =A0 | text =A0 =A0 =A0 =A0 =A0 =A0 | YES =A0| =A0 =A0 |=
NULL =A0 =A0 =A0 =A0 =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 |
>+----------------+------------------+------+-----+-------------------+----=
-------------------------+
>10 rows in set (0.00 sec)
>
>mysql> describe tag;
>+-------------+------------------+------+-----+---------+----------------+
>| Field =A0 =A0 =A0 | Type =A0 =A0 =A0 =A0 =A0 =A0 | Null | Key | Default =
| Extra =A0 =A0 =A0 =A0 =A0|
>+-------------+------------------+------+-----+---------+----------------+
>| id =A0 =A0 =A0 =A0 =A0| int(10) unsigned | NO =A0 | PRI | NULL =A0 =A0| =
auto_increment |
>| name =A0 =A0 =A0 =A0| varchar(255) =A0 =A0 | YES =A0| =A0 =A0 | NULL =A0=
=A0| =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0|
>| description | text =A0 =A0 =A0 =A0 =A0 =A0 | YES =A0| =A0 =A0 | NULL =A0=
=A0| =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0|
>+-------------+------------------+------+-----+---------+----------------+
>3 rows in set (0.01 sec)
>
>mysql> describe complexity_tag;
>+---------+------------------+------+-----+---------+-------+
>| Field =A0 | Type =A0 =A0 =A0 =A0 =A0 =A0 | Null | Key | Default | Extra |
>+---------+------------------+------+-----+---------+-------+
>| post_id | int(10) unsigned | NO =A0 | PRI | NULL =A0 =A0| =A0 =A0 =A0 |
>| version | int(10) unsigned | NO =A0 | PRI | NULL =A0 =A0| =A0 =A0 =A0 |
>| tag_id =A0| int(10) unsigned | NO =A0 | PRI | NULL =A0 =A0| =A0 =A0 =A0 |
>+---------+------------------+------+-----+---------+-------+
>3 rows in set (0.00 sec)
>
>
>mysql> show triggers\G
>*************************** 1. row ***************************
>=A0 =A0 =A0 =A0 =A0 =A0 =A0Trigger: bi_complexity_submission_trigger
>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Event: INSERT
>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Table: complexity_submission
>=A0 =A0 =A0 =A0 =A0 =A0Statement: 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
>=A0 =A0 =A0 =A0 =A0 =A0 =A0 Timing: AFTER
>=A0 =A0 =A0 =A0 =A0 =A0 =A0Created: NULL
>=A0 =A0 =A0 =A0 =A0 =A0 sql_mode:
>=A0 =A0 =A0 =A0 =A0 =A0 =A0Definer: momentum at localhost
>character_set_client: latin1
>collation_connection: latin1_swedish_ci
>=A0 Database Collation: latin1_swedish_ci
>
>
>Schema::Result::Complexity.pm
>__PACKAGE__->set_primary_key("post_id", "version");
>...
>__PACKAGE__->has_many(map_complexity_tag =3D> 'Momentum::Schema::Result::C=
omplexityTag', ['post_id', 'version']);
>__PACKAGE__->many_to_many(tags =3D> 'map_complexity_tag', 'tag_id');
>
>
>Schema::Result::Tag.pm
>__PACKAGE__->set_primary_key("id");
>...
>__PACKAGE__->has_many(map_complexity_tag =3D> 'Momentum::Schema::Result::C=
omplexityTag' =3D> 'tag_id');
>__PACKAGE__->many_to_many(complexities =3D> 'map_complexity_tag', ['post_i=
d','version']);
>
>
>Schema::Result::ComplexityTag.pm
>__PACKAGE__->set_primary_key("post_id", "version", "tag_id");
>...
>__PACKAGE__->belongs_to(complexity =3D> 'Momentum::Schema::Result::Complex=
ity', ['post_id', 'version']);
>__PACKAGE__->belongs_to(tag =3D> 'Momentum::Schema::Result::Tag', 'tag_id'=
);
>
>
>Complexity.pm controller ...
>sub test : Path('test') : Args(0) {
>=A0 =A0 my ( $self, $c ) =3D @_;
>=A0 =A0 $c->stash->{complexity} =3D
>=A0 =A0 =A0 $c->model('MomentumDB::Complexity')->find ( { post_id =3D> 7, =
version=3D>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 c=
ondition post_id yet :( at /usr/local/lib/perl5/site_perl/5.12.2/DBIx/Class=
/ResultSource.pm line 1471.
>=A0at /usr/local/lib/perl5/site_perl/5.12.2/DBIx/Class/Schema.pm line 1061
>DBIx::Class::Schema::throw_exception('Momentum::Schema=3DHASH(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=
=3DHASH(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/Re=
lationship/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_per=
l/5.12.2/Try/Tiny.pm line 100
>Try::Tiny::try('CODE(0x9c52ec0)', 'Try::Tiny::Catch=3DREF(0x9c471d0)') cal=
led at /usr/local/lib/perl5/site_perl/5.12.2/DBIx/Class/Relationship/Base.p=
m 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.c=
o.uk
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20120823/60a=
b2141/attachment-0001.htm
More information about the DBIx-Class
mailing list