[Dbix-class] Re: Complex(ish) select statement => DBIx::Class method

[Jason Gottshall]

fREW Schmidt wrote:
> 
> 
> On Thu, Mar 12, 2009 at 10:23 AM, fREW Schmidt <frioux at gmail.com 
> <mailto:frioux at gmail.com>> wrote:
> 
>     Hello friends!
> 
>     We have the following SQL:
> 
>         (select date from logParents where id = 62),
>         (select max(logstatus.date) from logstatus
>         join logChildren on logChildren.id = logStatus.child_id
>         join logParents on logParents.id = logChildren.parent_id
>         where parent_id = 62)
>         ) as elapsed
> 
> 
>     We'd like to make it a method for the logParent, so one could just
>     do $parent->elapsed_ms or something like that.  We are kindav at a
>     loss as to how to do this.  Any tips at all would be extremely helpful.
> 
> 
> Actually I pasted the SQL incorrectly...  here is what it should have been:
> 
> select datediff(millisecond, 
> 	(select date from logParents where id = 62), 
> 
> 	(select max(logstatus.date) from logstatus 
> 		join logChildren on logChildren.id = logStatus.child_id 
> 
> 		join logParents on logParents.id = logChildren.parent_id 
> 		where parent_id = 62)
> ) as elapsed
> 

What is datediff(), and it is absolutely necessary? Seems to me you 
could do the math on perl side, especially if you're using the 
InflateColumn::Datetime component. Assuming a few things about your 
schema config:

   log_parent->has_many(log_children)
   log_children->has_many(log_status)

   log_parent->date isa DateTime
   log_status->date isa DateTime


then you could create your method like this (untested, of course):

   package My::Schema::LogParent;

   sub elapsed_ms {
       my $self = shift;
       my $latest = $self->log_children->log_status
           ->get_column('date')->max;
       my $diff = $self->date->subtract_datetime($latest);
       return $diff->in_units('nanoseconds') / 100000;
   }

-- 
Jason Gottshall
jgottshall at capwiz.com