Re: [Dbix-class] Problem getting data from resultset

2012-07-07 Thread Patrick Meidl 
On Thu, Jul 05 2012, Patrick Meidl patr...@pantheon.at wrote:

 On Thu, Jul 05 2012, Kenneth S Mclane ksmcl...@us.ibm.com wrote:
 
  my $wb = 
  Spreadsheet::WriteExcel-new('/opt/catalyst/dbms/lib/dbms/output/output.xls');
  my $ws = $wb-add_worksheet('Output');
  my $col = 0; my $row = 0;
  my @fields = qw/ department.department_code account_code 
  account_name account_policy compliance.percent_compliant 
  metrics.num_servers metrics.num_subsystems progress.percent_complete /;
  This part works, I get my header row.
  foreach my $field (@fields) {
  $ws-write($row, $col, $field);
  
  $col++;
  }
  $col=0;
  $row++;
  This doesn't, I get an error that it can't locate the method, I tried 
  $ws-write($row, $col, $data-{$field)}; and I get no errors but no 
  data. I also tried removing the arrow, but then it thinks I have an 
  undeclared hash.
  foreach my $data ($sr-all) {
  foreach my $field (@fields) {
  $ws-write($row, $col, $data-$field);
  
  $col++;
  } 
  $row++;
  $col=0;
  }
 
 your code will only work for fields in the main table of your resultset,
 but will break for relations. for example,
 $data-department.department_code is not a valid accessor. you will have
 to parse your field definition and convert it into
 $data-department-department_code in order to retrieve the data.

I remembered that there is a more elegant solution for this (and also
more DBIc-related; the discussion got a bit OT in the direction of
general perl problems): you can define proxies for fields in a related
table. so in your main Result class (I assume it is called something
like 'Account'), you would write something like:

__PACKAGE__-belongs_to(
department,
YourApp::Schema::Result::Department,
{ department_id = department_id },
{
is_deferrable = 1, on_delete = CASCADE, on_update = CASCADE,
proxy = [ qw/department_code/ ],
},
);

then, if you have an Account result, you can call $data-department_code
directly, rather than $data-department-department_code.

for details, see
https://metacpan.org/module/DBIx::Class::Relationship::Base#attributes

HTH

patrick

-- 
Patrick Meidl  patr...@pantheon.at
Vienna, Austria .. http://gplus.to/pmeidl


___
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.scsys.co.uk

[Dbix-class] Re: Join Myself?

2012-07-07 Thread Steve Wells 
On Fri, Jul 6, 2012 at 11:33 AM, Steve Wells we...@cedarnet.org wrote:
 I'm trying to convert this SQL statement to DBIC and I'm failing miserably.

Let me clarify a bit…

Given this setup:

http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group

it is possible to convert the SQL statement:

SELECT m1.*
FROM messages m1 LEFT JOIN messages m2
 ON (m1.name = m2.name AND m1.id  m2.id)
WHERE m2.id IS NULL;

to DBIx::Class.

I have read the manual / tutorials etc but it doesn't seem possible to
add and extras (i.e. m1.id  m2.id) to your join command.

Is this true?

___
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.scsys.co.uk