DBIx::Class insert has many

Question

I'm using DBIx::Class and I have two Schemas:

use utf8;
package MyApp::Schema::Result::Person;

use Moose;
use MooseX::NonMoose;
use MooseX::MarkAsMethods autoclean => 1;
extends 'DBIx::Class::Core';

__PACKAGE__->table("person");

__PACKAGE__->add_columns(
    "id",
     { data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
);

__PACKAGE__->has_many(
    "addresses",
    "MyApp::Schema::Result::Address",
    { "foreign.person_id" => "self.id" },
    { cascade_copy => 0, cascade_delete => 0 },
);

1;

And:

use utf8;
package MyApp::Schema::Result::Address;

use Moose;
use MooseX::NonMoose;
use MooseX::MarkAsMethods autoclean => 1;
extends 'DBIx::Class::Core';

__PACKAGE__->table("address");

__PACKAGE__->add_columns(
    "id",
     { data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
     "person_id",
     { data_type => "integer", is_foreign_key => 1, is_nullable => 1 },
);

__PACKAGE__->belongs_to(
    "person",
     "MyApp::Schema::Result::Person",
     { id => "person_id" },
     {  
         is_deferrable => 0,
         join_type     => "LEFT",
         on_delete     => "NO ACTION",
         on_update     => "NO ACTION",
     },  
);

1;

What I am trying to do is add multiple addresses at once with a person object. I am doing that like so:

my $person = $c->model('DB::Person')->new_result({});
$person->addresses([
    {
        id => 1,
        person_id => 1,
    },
    {
        id => 2,
        person_id => 1,
    },
]);

$person->insert;

I followed this format from this article, but it doesn't seem to work. Only the person row gets inserted, but the addresses associated with it do not. I've also tried setting addresses to an arrayref of MyApp::Schema::Result::Address objects before inserting, but that doesn't work either. Does anyone know what I'm doing wrong? I don't get any errors, it just doesn't insert the addresses. In the article they use create instead of insert. Is it because of this? If so, is there a way to do this using insert or update?

score 2 · Answer 1 · answered Aug 16 '14 at 13:53

When you are adding related child records you don't need to specify the value of the foreign key for the children. The relationship should take care of this for you automatically. e.g. in your example person_id is unnecessary.

I suspect this may be causing an issue in the example. How do you know the person_id is really 1? It's an autoincrement column, and you're not explicitly passing in a value when you create the Person.

What happens with this:

-- In Person.pm --

__PACKAGE__->add_columns(
    "id",
     { data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
    "name",
     { data_type => "varchar"},
);

-- In Address.pm --

__PACKAGE__->add_columns(
    "id",
     { data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
     "person_id",
     { data_type => "integer", is_foreign_key => 1, is_nullable => 1 },
    "street",
     { data_type => "varchar"},

);

Then this insert code:

my $person = $c->model('DB::Person')->new_result({ name => "Bob" });
$person->addresses([
    {
        street => "Apple Street",
    },
    {
        street => "Orange Avenue",
    },
]);

$person->insert;

Borodin · Answer 2 · 2014-07-11T12:36:34.137

1

I would have thought your belongs_to relationship in Address should look like

{ 'foreign.id' => 'self.person_id' }

because id is ambiguous without specifying a table name.

You seem to have it right in the has_many relationship for your Person

edited Jul 11 '14 at 12:36

answered Jul 11 '14 at 12:29

Borodin

126,100
9
70
144

Hmm, yeah that's strange. The definitions were just created for me by DBIx::Class::Schema::Loader. They seem to work fine. – srchulo Jul 11 '14 at 15:22

score 0 · Answer 3 · answered Jul 11 '14 at 07:17

0

I'm pretty sure that it has to do with the Person object only created in memory, try using create instead.

Is there a reason you want to insert it after having assigned the addresses?

Note that you have two DBIx::Class::Result classes in one DBIx::Class::Schema.

You also should not override auto-increment column values as your database won't know that those ids are already used and try to do so later leading to a hard to track down error.

answered Jul 11 '14 at 07:17

Alexander Hartmaier

2,178
12
21

The reason I want to insert it after having assigned the addresses is because I am first using HTML::FormHandler to validate it, and HTML::FormHandler needs an item to validate the form, so I have to have the row before I make that call. Also, I'm not actually assigning the auto increment ids-- this was just for example. – srchulo Jul 11 '14 at 15:18

DBIx::Class insert has many

3 Answers3