Problems with set_sql and iterators

[prev] [thread] [next] [Date index for 2005/06/20]

This is a multi-part message in MIME format.
--------------000101080404040304020100
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

G'day everyone,

I'm new here.  I've searched the archives to see if this error has been 
mentioned recently but I haven't spotted it if is has.

I'm having a problem with set_sql and iterators.  I'm using set_sql to give me 
an aggregate method on a rather simple class:

	package Staff::Projects;

	use strict;
	use base 'Staff::DBI';  # Inherit from our application base class

	# Declare the name of our table
	__PACKAGE__->table( 'Projects' );

	# Declare our primary key field(s)
	__PACKAGE__->columns(Primary => 'StaffID', 'ProjectName', 'Allocation');
	__PACKAGE__->columns(TEMP => 'Total');

	# Select total time allocated to each project
	Staff::Projects->set_sql(total_times =>
         	"SELECT ProjectName, sum(Allocation) as Total
         	 FROM Projects
         	 GROUP BY ProjectName
         	 ORDER BY ProjectName"
	);


and everything works perfectly if I call it as follows:

	my @projects = Staff::Projects->search_total_times();
	foreach my $project (@projects) {
  	       print $project->projectname, "      ", $project->total(), "\n";
	}

however if I try to create an iterator:

	use Data::Dumper;
	my $project_it = Staff::Projects->search_total_times();

	print Dumper $project_it;

	while( my $project = $project_it->next) {
	        print $project->projectname, "      ", $project->total(), "\n";
	}

I get no ouput from my while loop even though Data::Dumper reports:

	$VAR1 = bless( {
                  '_class' => 'Staff::Projects',
                  '_place' => 0,
                  '_data' => [
                               {
                                 'projectname' => 'ABC',
                                 'total' => '265'
                               },
                               {
                                 'projectname' => 'NMO',
                                 'total' => '60'
                               },
                               {
                                 'projectname' => 'XYZ',
                                 'total' => '230'
                               }
                             ],
                  '_mapper' => []
                }, 'Class::DBI::Iterator' );

Now I partially understand why this is.  $project_it->next is being caught by 
the overload at line 38 in Class::DBI (apparently version 0.96).

   DB<1>
Class::DBI::CODE(0x83e4fd8)(/usr/local/share/perl/5.6.1/Class/DBI.pm:38):
38:             bool     => sub { not shift->_undefined_primary },

And it's correct, it *is* missing primary fields (StaffID and Allocation). 
However that doesn't help.  Aggregate functions aggregate information so of 
course data is going to be lost.  It's just a shame that some of this data 
includes fields which are otherwise primary keys.

I know I can get around it by not using iterators (as mentioned above) or by 
mangling my SQL to say:

Staff::Projects->set_sql(total_times =>
         "SELECT 1 as StaffID, ProjectName, sum(Allocation) as Allocation
          FROM Projects
          GROUP BY ProjectName
          ORDER BY ProjectName"
);

but the former doesn't handle large data sets and the latter is just ugly.

I guess my question is really whether there's yet another way of solving this 
problem or perhaps whether looking at the primary keys is the correct way of 
determining whether an element is true or not?

I've included SQL to create sample tables, classes and a test script to 
reproduce this problem.

All the best,

       Jacinta

        -- 
            ("`-''-/").___..--''"`-._          |  Jacinta Richardson         |
     `6_ 6  )   `-.  (     ).`-.__.`)  |  Perl Training Australia    |
     (_Y_.)'  ._   )  `._ `. ``-..-'   |      +61 3 9354 6001        |
   _..`--'_..-_/  /--'_.' ,'           | contact@xxxxxxxxxxxx.xxx.xx |
  (il),-''  (li),'  ((!.-'             |   www.perltraining.com.au   |



--------------000101080404040304020100
Content-Type: application/x-gzip;
 name="set_sql_error.tgz"
Content-Transfer-Encoding: base64
Content-Disposition: inline;
 filename="set_sql_error.tgz"

H4sIAMFvtkIAA+1X8XPaNhTOr/Nf8ZbuzuaOJDZg2JJrbgRol5YABdquu91xwijBrS1TSSTj
ev3f9yTb2KFhye5Kelv9/YCFn/T0pCd/31OrfXZ+tLdb2HbNbrguPm2n4Tr5Z4o9x664bq3e
qDvVPdtx7Lq9B+6O49JYCkk4wN57wn1vvr3fffb/KFoq/5IKebgIdjWH7dh2vVbbmv9GtbaR
/0q14e6BvauA8vjO8//kx6Ol4EdTnx0tKA/g4MZYCgpC4mrliW6PJLm8PD4e8Og99aSIX7aJ
JMfH7WWIo04M4wl0fSEhWkpYJP2AsBnISJIgfQXSDymQIIg8Iv2ICSNcwU+JceJLeLo518Gp
oIR784n2M1HjhVXC+RbcZxLi6fMu0HQz9wNqQc41+s11OThl9C9Zgk/GD7GX1HZwmjQYCWkZ
9kFjv5zroMOwSmj8k+2fGJ8NQy3h1/WSH7yAy4hT4s1vRWmt/Xyl4B6Sf/3966B3pwL/iv9r
DfX9O9VKwf+PgVz+sXW4CHcwxz3871Tsapb/quL/qu1WCv5/DCyI94FcrUkezwBS6KYCTAn+
mK2ACKG7mCcAT+CczSlH1r7kUQiZUYlBk61AULlcQHQJlF37PGIhRTq7xl0k04AKsMQS+Y8I
4PTj0ud0BsiJ0OfEC2gJriIq0A9OQJW/8dwXgJEIkHMMDSkQFoTjQ1IulBMvYgxJ0GdXcOPL
Oag4JpNBs/Wy+bwzmRycph0iZploPA5X4mNwPEMVU4t7Opv6jlkGM31eU74aUY9TaZYN2IJP
sNWUYkh8QTuc49KenoKz3VeK0Ty60f0xIZLqPXvYwIGSi/VM9vYBn43SSZqjCLeTJ5m6obh1
Yo6SDbNIJ0Bv/yHEu+8RBj7zguUME7CccpR6nFFnCRUXU5kODskHFPlr4gcq0eoNKr7Om4fK
POOUgacOCxWHhuFgKN/6E/iukeP/tGb56iJwH/8rsk/4H7tVsH+tgt0L/n8EbPB/VuRvEYFM
J+4QgWjJgSwWgR/X9/EQ/bErwmlTbHKqqUBVsUob1AipeOI2W+tXFphpOCbEnJW6UMOwPg4J
X8EHuoJLnwYzS5Q2OT9YhkxYg6Qn8mIc/3lbcXzivIehqL/N9b3ExMnudDTuXAy0l7Eqtc04
phEN9N1G33TyNxyqrj+gy/ykRjfuuB3ICQqRlbse4ARr7t4fdbqd1hhyoZaRfUMrC7ak5E/H
kzH+s2H/Ih0jstfPh/3XAzh7l3eXWfvDdme4Yd3XWlHQ9P8Xmv+xzsHjeogHcSdz3MP/FeT+
df3vNuq6/i/4/3HQGnaa4w6Mm2fdzpoywFrTQsKXa5Y4743X7V5/DL3X3W5Wa+aoQ/9/0xy2
fmsOrYpduqN3RmLbfWsCOu+NOsOxsvfjgMBK4irDM5+LhBi7JG01Z1hnClGGli9XZdC1dNkY
RMJXs5XhrZK8kirf3zS7rzsjsGxUgBeohUoJRgtOpGpUYIAlLgxn6s8FDaaoO0yLxRvfU482
vaYBNn62S2pZsZdozrQt0j1rVXhBFK23uLzPjVPP+TmjUq5M7RDLbB1OBVcr5uqSM0p9yYjl
HF0QhivjylU+pGd4vTKzp87CrV+z+bbfRYsN2aARCfVmhHifUg3XhVdLSpmA5rUOfrSaMaoj
7I3eagWdhb6KBj/fzE2TsVtuHBvzPqVcJms4476Ykng7XnXb+e1ouJmbgbrqadWOFrpvxa3B
y2wrvgjm9lZsHKLsoK/P0S2Jzclr7oyYTqVac/VKz1r4cJPtihGb65kZau6X9kZmR1b8wu46
ufGNbf57F31lr99h1+H9/u4PZb8rvl/+2e7amX17fMn4qq3Ksm/NYAUKFChQoECBAgUKFChQ
4CH4G5auswQAKAAA
--------------000101080404040304020100--