[Dbix-class] copying rows from select

[Wolfgang Kinkeldei]

Hi Roland,

Am 13.01.2012 um 12:33 schrieb Roland Philibert:

> Hi all,
> I am new on the subject..please excuse my ignorance!

I am just a user of DBIx::Class, so there might be even better ways than I suggest.

> Am trying to find the most efficient way of doing a simple copy of rows
> such as in sql: INSERT INTO table (col1, col2, col3, ...) SELECT col1,
> col2, col3, ... FROM table   WHERE primarykey = xx

If you intend to copy single rows, you might like to use the ->copy() method of DBIx::Class::Row

my $original_record = $schema->resultset('MyTable')->find($id);
my $new_record = $original_record->copy();

However, the SQL generated will use two statements instead of one in your pure-SQL example above.


If you plan to copy many rows at once, you could retrieve your rows, convert your result into an array of hashrefs and feed this structure into DBIx::Class::Schema's ->populate() method. The code looks a bit more complicated, but also yields only 2 SQL Statements, the number of records is irrelevant.

my $resultset = $schema->resultset('MyTable')->search( { conditions }, { columns => [ ... ] } );
$resultset->result_class('DBIx::Class::ResultClass::HashRefInflator');
my @records = $resultset->all;
$schema->populate(MyTable => \@records) if @records;

The downside of the latter solution is that you explicitly must build the list of columns you want to copy yourself (or programatically by scanning thru your table's columns). Otherwise your primary key or unique constraints might complain their values' duplication.


I hope I could help.

> 
> Many thanks.
> Roland
> Aptina (UK) Limited, Century Court, Millennium Way, Bracknell, Berkshire, RG12 2XT. Registered in England No. 06570543.
> 
> This e-mail and any attachments contain confidential information and are solely for the review and use of the intended recipient. If you have received this e-mail in error, please notify the sender and destroy this e-mail and any copies.


[...]

Best,

Wolfgang Kinkeldei

-- 

' /\_/\ ' .print[split//,"".(($/=q|Cms)+-03467:;<=|)=~tr!C-z -B! -z!)x
'( o.o )' .$/]->[hex]foreach split qr<>,qq+1ecd039ad65b025b8063475b+||
' > ^ < ' .q<!-- Wolfgang Kinkeldei - wolfgang at kinkeldei dot de -->