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

[Matt S Trout]

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()'
>   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/