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

[David Ihnen]

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'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 =3D 62), =

>
> 	(select max(logstatus.date) from logstatus =

> 		join logChildren on logChildren.id =3D logStatus.child_id =

>
> 		join logParents on logParents.id =3D logChildren.parent_id =

> 		where parent_id =3D 62)
> ) as elapsed

If it was expressed without subselects...

select datediff(millisecond, lp.date, max(ls.date)) as elapsed
from logParents
left outer join logstatus ls on (lp.id =3D ls.parent_id)
left outer join logChildren lc on (lc.parent_id =3D lp.id)
where lp.id =3D 62
group by ls.id

I think that makes it more clear.  Given the proper relationships set up =

couldn't that just be something like...

sub elapsed_ms {
my $self =3D shift;
my $id =3D shift;
$self->search(
  { me.id =3D> $id },
  { +select =3D> [ \"datediff(millisecond, logparents.date, logstatus.date)=
" ]
  , +as =3D> [ elapsed ]
  , prefetch =3D> [ 'logstatus', 'logchildren' ]
  , order_by =3D> 'logstatus.date desc'
  })->first->get_column->('elapsed');
}

Though the order_by is less efficient than the group by, I'm sure.

David


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20090312/c8b=
c937e/attachment.htm