[Dbix-class] Subselect, max, now()

[Paul Makepeace]

On 9/19/07, Matt S Trout <dbix-class at trout.me.uk> wrote:
> On Wed, Sep 19, 2007 at 12:32:10AM +0100, Paul Makepeace wrote:
> > OK, I give up. Could someone please help convert this SQL into DBIx::Class,
> >
> >   select story.date_time, story.source_uid, story.headline
> >   from story where story.publication_uid = 23 and date_time = (
> >     select max(story.date_time)
> >     from story join publication on story.publication_uid = publication.uid
> >     where publication.uid = 23 and story.date_time < now());
> >
> > I sorta managed the subselect but couldn't get it to treat now() as a function,
> >
> > my @SEARCH_MAX_DATE_TIME_ARGS = (
> >       join => [qw/story_publication/],
> >       select => [\'max(me.date_time)'],
> >       as => [qw/max_date_time/],
> > );
> >
> > sub search_max_date_time {
> >       my ($obj, $search, $args) = @_; $args ||= {};
> >       $obj->search($search,   { @SEARCH_MAX_DATE_TIME_ARGS, %$args });
> > }
> >
> > Controller:
> >   my ($max_date_time) :Stashed =
> > $ds_ro->resultset('Story')->search_max_date_time(
> >       { %$search, 'me.date_time' => { '<' =>  \'now()' }})->next;
>                                    => \'< now()'

K, thanks.

Any other hints on getting the rest of it working?? All this code
actually only does the subselect portion of what I'm after. I.e.
additionally,

   select story.date_time, story.source_uid, story.headline
   from story where story.publication_uid = 23 and date_time = [stuff
we've just done]

(There's a repetition of the story.publication_uid = ? bit, fwiw.)

P

> >   if ($max_date_time) {
> >       $max_date_time = $max_date_time->get_column('max_date_time');
> >   }
> >
> > Thanks! Other random style comments welcome.
> >
> > P
> >
> > _______________________________________________
> > List: http://lists.rawmode.org/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.rawmode.org
>
> --
>       Matt S Trout       Need help with your Catalyst or DBIx::Class project?
>    Technical Director                    http://www.shadowcat.co.uk/catalyst/
>  Shadowcat Systems Ltd.  Want a managed development or deployment platform?
> http://chainsawblues.vox.com/            http://www.shadowcat.co.uk/servers/
>
> _______________________________________________
> List: http://lists.rawmode.org/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.rawmode.org
>