FAQ
Hello,
I am new to DBIx Class and I am trying to insert a record that
contains a many to many relationship. I *think* that inserting into
the record should populate the necessary foreign tables, but I can't
get that to work. Do I need to populate them separately? That seems
wrong. This is how I have the relationship setup.

(a many to many relationship between writers and songs)

Package App::Schema::Result::Songs;

__PACKAGE__->has_many('writers_songs',
'App::Schema::Result::WritersSongs',
{ 'foreign.song' => 'self.song' } );
__PACKAGE__->many_to_many('writers' => 'writers_songs','writer');

.....

Package App::Schema::Result::Writers;

__PACKAGE__->has_many('writers_songs',
'App::Schema::Result::WritersSongs',
{ 'foreign.writer' => 'self.writer' } );

__PACKAGE__->many_to_many('songs' => 'writers_songs', 'song');

....

Package App::Schema::Result::WritersSongs;

__PACKAGE__->belongs_to('writer',
'App::Schema::Result::Writers',
{ 'foreign.writer' => 'self.writer' } );

__PACKAGE__->belongs_to('song',
'App::Schema::Result::Songs',
{ 'foreign.song' => 'self.song' } );

...
So, then I try to run

my $song = $schema->resultset('Songs')->find_or_create({
'artist' => {
'artist_name' => 'Beyonce'
},
'writers_songs' => [
{
'writer' => {
'name' => 'B.Knowles'
}
},
{
'writer' => {
'name' => 'J.G.Scheffer'
}
},
{
'writer' => {
'name' => 'W.Wilkins'
}
},
{
'writer' => {
'name' => 'Rico Love'
}
}
],
'title' => 'Sweet Dreams',
});

And it fails to create the writers_songs record, even if the writers
already exist. I ran a trace to see what is going on, and this is
what I gathered,

It tries to select from writers_songs,
SELECT me.writer, me.song, me.publisher, me.percentage FROM
writers_songs me WHERE ( ( me.song = ? AND me.writer IS NULL ) )

but it never attempts to get the writer foreign key. SO, when it
tries to insert into writers_songs with a null writer, it fails...

INSERT INTO writers_songs ( song, writer) VALUES ( ?, ? ): '17', 'NULL'

So, the problem appears to be that it never attempts to select from
the writers table, or insert into it in the case where the writer
doesn't yet exist. Any ideas?

Thanks!

- Jon

Search Discussions

  • Eden Cardim at Dec 3, 2009 at 8:13 am
    "Jon" == Jon <jon@texttoall.com> writes:
    <snip>

    Jon> So, the problem appears to be that it never attempts to select
    Jon> from the writers table, or insert into it in the case where the
    Jon> writer doesn't yet exist. Any ideas?

    Try running with the DBIC_MULTICREATE_DEBUG environment variable set to
    a true value then show us the result along with the table class declarations
    (verbatim, all of it).

    --
    Eden Cardim Need help with your Catalyst or DBIx::Class project?
    Code Monkey http://www.shadowcat.co.uk/catalyst/
    Shadowcat Systems Ltd. Want a managed development or deployment platform?
    http://edenc.vox.com/ http://www.shadowcat.co.uk/servers/
  • Peter Rabbitson at Dec 3, 2009 at 9:08 am

    Jon wrote:
    Hello,
    I am new to DBIx Class and I am trying to insert a record that
    contains a many to many relationship. I *think* that inserting into
    the record should populate the necessary foreign tables, but I can't
    get that to work. Do I need to populate them separately? That seems
    wrong. This is how I have the relationship setup.

    ...

    And it fails to create the writers_songs record, even if the writers
    already exist. I ran a trace to see what is going on, and this is
    what I gathered,

    It tries to select from writers_songs,
    SELECT me.writer, me.song, me.publisher, me.percentage FROM
    writers_songs me WHERE ( ( me.song = ? AND me.writer IS NULL ) )

    but it never attempts to get the writer foreign key. SO, when it
    tries to insert into writers_songs with a null writer, it fails...

    INSERT INTO writers_songs ( song, writer) VALUES ( ?, ? ): '17', 'NULL'

    So, the problem appears to be that it never attempts to select from
    the writers table, or insert into it in the case where the writer
    doesn't yet exist. Any ideas?
    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.
  • Jon at Dec 3, 2009 at 4:09 pm

    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'
    }
    };
  • Peter Rabbitson at Dec 3, 2009 at 4:35 pm
    This version is more than 7 months old. Upgrade.
  • Jon at Dec 3, 2009 at 5:40 pm
    This version is more than 7 months old. Upgrade.
    Yep, that fixed it. Jeez, dumb mistake by me. At least I learned
    about DBIC_MULTICREATE_DEBUG.

    Thanks!

    - Jon

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbix-class @
categoriesperl, catalyst
postedDec 2, '09 at 4:05p
activeDec 3, '09 at 5:40p
posts6
users3
websitedbix-class.org
irc#dbix-class

People

Translate

site design / logo © 2021 Grokbase