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

[John Napiorkowski]

--- Paul Makepeace <paulm at paulm.com> wrote:

> 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

Hi,

When I get stuck like this I find turning on SQL debug
output so that I can see the SQL that DBIC is trying
to make really helps me:

Should be something like:

$ds_ro->resultset('Story')->storage->debug(1);

Remember that DBIC doesn't hit the database until you
try to retrieve something, so you won't see the SQL
debug info until that point.

Take a look at what is being created versus what you
are hoping for.  Good Luck!

--John

> 
> > >   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
> >
> 
> _______________________________________________
> 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
> 



       
____________________________________________________________________________________
Moody friends. Drama queens. Your life? Nope! - their life, your story. Play Sims Stories at Yahoo! Games.
http://sims.yahoo.com/