[Dbix-class] create() has_one problems

[Jason Kohles]

On Jan 14, 2008, at 6:40 AM, Carl Franks wrote:

> I'm unable to use create() to create a new record with a has_one  
> relationship.
> The docs (ResultSet/create) say: for has_one rels, "pass an arrayref
> of hashrefs".
>
This looks like a doc bug, I don't see any reason to be passing an  
arrayref when has_one can only refer to one object.  I suspect it was  
supposed to instruct you to use a hashref for belongs_to/might_have/ 
has_one (relationships where accessor = 'single') and an arrayref for  
has_many/many_to_many (relationships where accessor = 'multi').

> If I pass an arrayref containing a hashref, like so:
>
> $rs->create({
>    text_col => 'filler2',
>    user => [ { name => 'foo' } ]
> } );
>
> I get this error message - I don't know where the "NAME" in the SQL  
> comes from:
>

> SELECT me.id, me.master, me.name FROM user me WHERE ( ( ( ( me.id NAME
> ? ) ) ) ): 'foo'
> DBI Exception: DBD::SQLite::db prepare_cached failed: near "NAME":
> syntax error(1) at dbdimp.c line 271 [for Statement "SELECT me.id,
> me.master, me.name FROM user me WHERE ( ( ( ( me.id NAME ? ) ) ) )"]
> at /opt/perl-5.8.8/lib/site_perl/5.8.8/DBIx/Class/Schema.pm line 945
>

Looks like it's the result of ->find interpreting your user wrong  
(because it didn't actually expect to see an arrayref there), and  
trying to turn that into part of the query.

> If I get rid of the arrayref, and just pass a hashref for the rel  
> data, like so:
>
> $rs->create({
>    text_col => 'filler2',
>    user => { name => 'foo' }
> } );
>
> The inserts work, but the related (user) table doesn't get the
> belongs_to id set.
> Setting DBIC_TRACE shows the SQL being generated:
>
> SELECT me.id, me.master, me.name FROM user me WHERE ( me.name = ? ):  
> 'foo'
> BEGIN WORK
> INSERT INTO master (id, text_col) VALUES (?, ?): 'NULL', 'filler2'
> SELECT COUNT( * ) FROM user me WHERE ( name = ? ): 'foo'
> INSERT INTO user (name) VALUES (?): 'foo'
> COMMIT
>
> I don't know why it's trying to retrieve a has_one rel (user) before
> the main entry (master) is even inserted.

Internally, what it's doing when you pass a hashref for a 'single'  
object (belongs_to/might_have/has_one) is something like this:

my $master = $rs->new( { text_col => 'filler2' } );
my $user = $master->find_or_new_related( { name => 'foo' } );
$master->user( $user );
$master->insert;

So the initial select is from the find_or_new_related attempting to  
figure out if it already exists or not.

>
> The "SELECT COUNT(*)" also looks suspicious - is that intended?
>

This is the result of ->insert() using ->count() to determine if the  
related object that it's about to insert into the database is already  
in storage or not.  I think what's happening is that one of your  
relationships is causing the initial select, and the other one is  
causing the count.

> Am I using this wrong?
>

As far as I can tell it _should_ work, but I generally opt to create  
the related objects directly, which may help to avoid the problem you  
are encountering...

$rs->create({
    text_col => 'filler2',
    user => $schema->resultset( 'User' )->find_or_create( name =>  
'foo' ),
} );


> I'm using DBIx::Class 0.08008, DBD::SQLite 1.14, sqlite 3.4.2-3.
> Copied below is the 2 relevant table classes (with irrelevant cols and
> rels deleted for clarity).
>
> package MySchema::Master;
> use base 'DBIx::Class';
>
> __PACKAGE__->load_components(qw/
>    InflateColumn::DateTime Core
> /);
>
> __PACKAGE__->table("master");
>
> __PACKAGE__->add_columns(
>    id             => { data_type => "INTEGER" },
>    text_col       => { data_type => "TEXT" },
> );
>
> __PACKAGE__->set_primary_key("id");
>
> __PACKAGE__->has_one( user => 'MySchema::User', 'master' );
>
> 1;
>
> package MySchema::User;
> use base 'DBIx::Class';
>
> __PACKAGE__->load_components(qw/ Core /);
>
> __PACKAGE__->table("user");
>
> __PACKAGE__->add_columns(
>    id     => { data_type => "INTEGER" },
>    master => { data_type => "INTEGER" },
>    name   => { data_type => "TEXT" },
> );
>
> __PACKAGE__->set_primary_key("id");
>
> __PACKAGE__->belongs_to( master => 'MySchema::Master', 'id' );
>
> 1;
>
> _______________________________________________
> 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.rawmode.org
>

-- 
Jason Kohles, RHCA RHCDS RHCE
email at jasonkohles.com - http://www.jasonkohles.com/
"A witty saying proves nothing."  -- Voltaire