[Dbix-class] many to many insert
Jon
jon at texttoall.com
Thu Dec 3 16:09:30 GMT 2009
> What version of DBIC. Also show the column definitions for the 3 sources
> involved. Also do you have a set_primary_key everywhere? Also provide
> what Eden asked for.
DBIx::Class - .08103
Here is a slimmed down definition for the three relevant tables,
package App::Schema::Result::Songs;
__PACKAGE__->table("songs");
__PACKAGE__->add_columns(
"song",
{ data_type => "INT", default_value => undef, is_nullable => 0, size => 11 },
"billboard_id",
{ data_type => "INT", default_value => undef, is_nullable => 1, size => 11 },
"song_parent",
<SNIP>
"title",
<SNIP>
"artist",
<SNIP>
"album",
<SNIP>
"publisher",
<SNIP>
"year",
<SNIP>
"song_info_url",
<SNIP>
"last_updated",
<SNIP>
);
__PACKAGE__->set_primary_key("song");
__PACKAGE__->add_unique_constraint("billboard_id", ["billboard_id"]);
__PACKAGE__->belongs_to(song_parent => 'App::Schema::Result::Songs',
'song_parent');
__PACKAGE__->belongs_to(artist => 'App::Schema::Result::Artists','artist');
__PACKAGE__->belongs_to(album => 'App::Schema::Result::Albums','album');
__PACKAGE__->belongs_to(publisher => 'App::Schema::Result::Publishers',
'publisher');
__PACKAGE__->has_many('writers_songs',
'App::Schema::Result::WritersSongs',
{ 'foreign.song' => 'self.song' } );
__PACKAGE__->many_to_many('writer' => 'writers_songs','writer');
package App::Schema::Result::Writers;
__PACKAGE__->table("writers");
__PACKAGE__->add_columns(
"writer",
{ data_type => "INT", default_value => undef, is_nullable => 0, size => 11 },
"name",
<SNIP>
"name_first",
<SNIP>
"name_last",
<SNIP>
);
__PACKAGE__->set_primary_key("writer");
__PACKAGE__->add_unique_constraint("name", ["name"]);
__PACKAGE__->has_many('writers_songs',
'App::Schema::Result::WritersSongs',
{ 'foreign.writer' => 'self.writer' } );
__PACKAGE__->many_to_many('song' => 'writers_songs', 'song');
package App::Schema::Result::WritersSongs;
__PACKAGE__->table("writers_songs");
__PACKAGE__->add_columns(
"writer",
{ data_type => "INT", default_value => undef, is_nullable => 0, size => 11 },
"song",
{ data_type => "INT", default_value => undef, is_nullable => 0, size => 11 },
"publisher",
{ data_type => "INT", default_value => undef, is_nullable => 1, size => 11 },
"percentage",
{ data_type => "DOUBLE", default_value => undef, is_nullable => 0,
size => 64 },
);
__PACKAGE__->set_primary_key("writer", "song");
__PACKAGE__->belongs_to('writer',
'App::Schema::Result::Writers',
{ 'foreign.writer' => 'self.writer' } );
__PACKAGE__->belongs_to('song',
'App::Schema::Result::Songs',
{ 'foreign.song' => 'self.song' } );
__PACKAGE__->belongs_to(publishers => 'App::Schema::Result::Publishers',
'publisher');
The debug output with DBIC_TRACE=1 and DBIC_MULTICREATE_DEBUG=1 is below.
I think it goes wrong on the 5th statement, "SELECT me.writer,
me.song, me.publisher, me.percentage FROM writers_songs me WHERE ( (
me.writer IS NULL AND 1 = 0 ) ):" Why would id be checking for writer
= null? I pasted the data structure I'm inserting below as well.
Thanks a lot for the help.
SELECT me.song, me.billboard_id, me.song_parent, me.title, me.artist,
me.album, me.publisher, me.year, me.song_info_url, me.last_updated
FROM songs me WHERE ( me.billboard_id = ? ): '10373447'
MC App::Schema::Result::Songs=HASH(0xcaea60) constructing artist via
find_or_new at /usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm
line 116.
SELECT me.artist, me.billboard_artist_id, me.artist_name,
me.last_updated FROM artists me WHERE ( me.artist_name = ? ):
'Beyonce'
MC App::Schema::Result::Songs=HASH(0xcaea60) constructing
writers_songs via find_or_new_related at
/usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 122.
SELECT me.writer, me.song, me.publisher, me.percentage FROM
writers_songs me WHERE ( ( me.writer IS NULL AND 1 = 0 ) ):
MC App::Schema::Result::WritersSongs=HASH(0xcd1994) constructing
writer via find_or_new_related at
/usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 122.
SELECT me.writer, me.name, me.name_first, me.name_last FROM writers me
WHERE ( ( me.name = ? AND 1 = 0 ) ): 'B.Knowles'
MC App::Schema::Result::Writers=HASH(0xcd3c1c) uninserted
writers_songs App::Schema::Result::WritersSongs=HASH(0xcd1994) (1 of
1)
MC App::Schema::Result::WritersSongs=HASH(0xcd1994) uninserted writer
App::Schema::Result::Writers=HASH(0xcd3c1c) at
/usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 226.
MC App::Schema::Result::WritersSongs=HASH(0xcd1994) uninserted song
App::Schema::Result::Songs=HASH(0xcaea60) at
/usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 226.
MC App::Schema::Result::Songs=HASH(0xcaea60) uninserted writers_songs
App::Schema::Result::WritersSongs=HASH(0xcd1994) (1 of 4)
MC App::Schema::Result::Songs=HASH(0xcaea60) constructing
writers_songs via find_or_new_related at
/usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 122.
SELECT me.writer, me.song, me.publisher, me.percentage FROM
writers_songs me WHERE ( ( me.writer IS NULL AND 1 = 0 ) ):
MC App::Schema::Result::WritersSongs=HASH(0xcd137c) constructing
writer via find_or_new_related at
/usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 122.
SELECT me.writer, me.name, me.name_first, me.name_last FROM writers me
WHERE ( ( me.name = ? AND 1 = 0 ) ): 'J.G.Scheffer'
MC App::Schema::Result::Writers=HASH(0xcd322c) uninserted
writers_songs App::Schema::Result::WritersSongs=HASH(0xcd137c) (1 of
1)
MC App::Schema::Result::WritersSongs=HASH(0xcd137c) uninserted writer
App::Schema::Result::Writers=HASH(0xcd322c) at
/usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 226.
MC App::Schema::Result::WritersSongs=HASH(0xcd137c) uninserted song
App::Schema::Result::Songs=HASH(0xcaea60) at
/usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 226.
MC App::Schema::Result::Songs=HASH(0xcaea60) uninserted writers_songs
App::Schema::Result::WritersSongs=HASH(0xcd137c) (2 of 4)
MC App::Schema::Result::Songs=HASH(0xcaea60) constructing
writers_songs via find_or_new_related at
/usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 122.
SELECT me.writer, me.song, me.publisher, me.percentage FROM
writers_songs me WHERE ( ( me.writer IS NULL AND 1 = 0 ) ):
MC App::Schema::Result::WritersSongs=HASH(0xcd358c) constructing
writer via find_or_new_related at
/usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 122.
SELECT me.writer, me.name, me.name_first, me.name_last FROM writers me
WHERE ( ( me.name = ? AND 1 = 0 ) ): 'W.Wilkins'
MC App::Schema::Result::Writers=HASH(0xcd4040) uninserted
writers_songs App::Schema::Result::WritersSongs=HASH(0xcd358c) (1 of
1)
MC App::Schema::Result::WritersSongs=HASH(0xcd358c) uninserted writer
App::Schema::Result::Writers=HASH(0xcd4040) at
/usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 226.
MC App::Schema::Result::WritersSongs=HASH(0xcd358c) uninserted song
App::Schema::Result::Songs=HASH(0xcaea60) at
/usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 226.
MC App::Schema::Result::Songs=HASH(0xcaea60) uninserted writers_songs
App::Schema::Result::WritersSongs=HASH(0xcd358c) (3 of 4)
MC App::Schema::Result::Songs=HASH(0xcaea60) constructing
writers_songs via find_or_new_related at
/usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 122.
SELECT me.writer, me.song, me.publisher, me.percentage FROM
writers_songs me WHERE ( ( me.writer IS NULL AND 1 = 0 ) ):
MC App::Schema::Result::WritersSongs=HASH(0xcd4550) constructing
writer via find_or_new_related at
/usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 122.
SELECT me.writer, me.name, me.name_first, me.name_last FROM writers me
WHERE ( ( me.name = ? AND 1 = 0 ) ): 'Rico Love'
MC App::Schema::Result::Writers=HASH(0xcd4160) uninserted
writers_songs App::Schema::Result::WritersSongs=HASH(0xcd4550) (1 of
1)
MC App::Schema::Result::WritersSongs=HASH(0xcd4550) uninserted writer
App::Schema::Result::Writers=HASH(0xcd4160) at
/usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 226.
MC App::Schema::Result::WritersSongs=HASH(0xcd4550) uninserted song
App::Schema::Result::Songs=HASH(0xcaea60) at
/usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 226.
MC App::Schema::Result::Songs=HASH(0xcaea60) uninserted writers_songs
App::Schema::Result::WritersSongs=HASH(0xcd4550) (4 of 4)
MC App::Schema::Result::Songs=HASH(0xcaea60) constructing publisher
via find_or_new at
/usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 116.
SELECT me.publisher, me.name, me.address_1, me.address_2, me.zip,
me.phone, me.publisher_parent, me.flat_royalty_pcnt, me.contact_name,
me.contact_email, me.contact_phone FROM publishers me WHERE ( me.name
= ? ): 'Columbia'
BEGIN WORK
MC App::Schema::Result::Songs=HASH(0xcaea60) pre-reconstructing artist
App::Schema::Result::Artists=HASH(0xcd11c0)
SELECT me.artist, me.billboard_artist_id, me.artist_name,
me.last_updated FROM artists me WHERE ( me.artist = ? ): '2'
MC App::Schema::Result::Songs=HASH(0xcaea60) pre-reconstructing
publisher App::Schema::Result::Publishers=HASH(0xcd94b4)
SELECT me.publisher, me.name, me.address_1, me.address_2, me.zip,
me.phone, me.publisher_parent, me.flat_royalty_pcnt, me.contact_name,
me.contact_email, me.contact_phone FROM publishers me WHERE ( (
me.publisher = ? OR me.name = ? ) ): '2', 'Columbia'
MC App::Schema::Result::Songs=HASH(0xcaea60) inserting (artist, 2,
billboard_id, 10373447, title, Sweet Dreams, publisher, 2)
INSERT INTO songs ( artist, billboard_id, publisher, title) VALUES (
?, ?, ?, ? ): '2', '10373447', '2', 'Sweet Dreams'
MC App::Schema::Result::Songs=HASH(0xcaea60) fetching missing PKs song
MC App::Schema::Result::Songs=HASH(0xcaea60) re-creating writers_songs
App::Schema::Result::WritersSongs=HASH(0xcd1994) at
/usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 376.
SELECT me.writer, me.song, me.publisher, me.percentage FROM
writers_songs me WHERE ( ( me.song = ? AND me.writer IS NULL ) ): '21'
MC App::Schema::Result::WritersSongs=HASH(0xcd45a4) uninserted writer
App::Schema::Result::Writers=HASH(0xcd3c1c) at
/usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 226.
MC App::Schema::Result::WritersSongs=HASH(0xcd45a4) uninserted song
App::Schema::Result::Songs=HASH(0xcaea60) at
/usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 226.
MC App::Schema::Result::WritersSongs=HASH(0xcd45a4) pre-reconstructing
song App::Schema::Result::Songs=HASH(0xcaea60)
SELECT me.song, me.billboard_id, me.song_parent, me.title, me.artist,
me.album, me.publisher, me.year, me.song_info_url, me.last_updated
FROM songs me WHERE ( ( me.billboard_id = ? OR me.song = ? ) ):
'10373447', '21'
MC App::Schema::Result::WritersSongs=HASH(0xcd45a4) inserting (writer,
, song, 21)
INSERT INTO writers_songs ( song, writer) VALUES ( ?, ? ): '21', 'NULL'
DBI Exception: DBD::mysql::st execute failed: Column 'writer' cannot
be null [for Statement "INSERT INTO writers_songs ( song, writer)
VALUES ( ?, ? )" with ParamValues: 0='21', 1=undef] at
/usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Schema.pm line 994
Data Structure:
$VAR1 = {
'artist' => {
'artist_name' => 'Beyonce'
},
'billboard_id' => 10373447,
'writers_songs' => [
{
'writer' => {
'name' => 'B.Knowles'
}
},
{
'writer' => {
'name' => 'J.G.Scheffer'
}
},
{
'writer' => {
'name' => 'W.Wilkins'
}
},
{
'writer' => {
'name' => 'Rico Love'
}
}
],
'title' => 'Sweet Dreams',
'publisher' => {
'name' => 'Columbia'
}
};
More information about the DBIx-Class
mailing list