Re: Problems with set_sql and iterators

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

Tony Bowden wrote:
> On Mon, Jun 20, 2005 at 05:50:52PM +1000, Jacinta Richardson wrote:
> 
>>	# 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"
>>	);
> 
> 
> As you say, it's a PK issue. As you're grouping by ProjectName, surely
> you should be declaring that as the sole PK?

I've simplified the problem in what I wrote rather than providing the full 
project.

Declaring the ProjectName as the sole primary key might make Class::DBI happy 
but risks confusing anyone who ever has to deal with the code in the future.  If 
the ProjectName was the sole element of the primary key then this aggregation 
wouldn't be necessary, or even possible (in a strict database sense).  Instead 
staff can be allocated to more than one project and each project can have 
multiple staff members allocated to it.

You have highlighted one problem in my spec, and that is that I should have:

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

as we don't want the staff member allocated to the same project twice, with 2 
different allocations, but this still doesn't solve the problem.

> If you need to use this class to access this table in its 'normal' form
> elsewhere, then turn this version of it into a different class (think of
> it as a cheap VIEW) that can have ProjectName as a single PK.

That is certainly an option which I will look into, thankyou for the advice. 
However, this is one case in many where I've run into this problem.  Creating 
views for each and every aggregate or otherwise specialist search is less than 
ideal.

Further, I can see future maintainers happily adding a new search or two and 
then wondering why one works perfectly and the other doesn't.  Or why their 
search works when called in array context but not in an iterator context.

The fact that I *know* the search returns data, can access that data, but can't 
iterate over seems to be a problem.  It seems to me that either object creation 
without defined primary keys should fail (so failing at the search stage) or 
that both iteration and array contexts should work.

Upon more thought I guess I'm challenging the idea that an object whose primary 
key is undefined but otherwise contains data should be considered false.  I'm 
struggling to find any situation that *I* would expect that result.  I'm sure 
that there situations, so if you could expand on one or two that would be 
appreciated.

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   |