[Dbix-class] Complex query in custom resultsource file.

[edwrb .]

Thanks Matt.  That 'as' trick took care of the SQL parsing issue.

On a related note, is there a best practice for iterating through the resul=
t-set from these
custom resource files by chance?  I thought something like the following wo=
uld work:

  my $rs =3D [ $schema->resultset( 'MainRecordQuery' )->search( {},
    {
      bind  =3D> [ $category, 'Y', 'thumbnail', 'main']
    }
  ) ];

while (my $data =3D $rs->next) {

    print $data->thumbnail . "\n";
}

but it fails with the following error:

'Can't call method "next" on unblessed reference'  (from the while loop)

A closer look at the data structure coming back from the resultsource looks=
 like it's an array of hashes?

Cheers,

-ed


> Date: Sun, 10 Feb 2008 10:23:07 +0000
> From: dbix-class at trout.me.uk
> To: dbix-class at lists.scsys.co.uk
> Subject: Re: [Dbix-class] Complex query in custom resultsource file.
> =

> On Sat, Feb 09, 2008 at 09:36:03PM +0000, edwrb . wrote:
> > =

> > Hi there,
> > =

> >   So I'm trying to implement a complex query in DBIx::Class using a cus=
tom
> >   ResultSource file, but the query ends up always failing due to some e=
xtra
> >   stuff that is being automatically tacked on to the query.  Here's my =
result
> >   source file:
> > =

> > package MyApp::Schema::ContentView;
> > =

> > use base qw/DBIx::Class/;
> > =

> > __PACKAGE__->load_components(qw/PK::Auto Core/);
> > __PACKAGE__->table('content');
> > =

> > my $source =3D __PACKAGE__->result_source_instance();
> > my $new_source =3D $source->new( $source );
> > $new_source->source_name( 'MainRecordQuery' );
> > =

> > my $sql =3D<<SQL;
> > SELECT me.content_id
> >      , '' as 'main'
> >      , '' as 'thumbnail'
> >   FROM  content me
> >   JOIN  category category ON ( category.category_id =3D me.category_id )
> >  WHERE  category.category_name =3D ?
> >    AND  me.active_flg          =3D ? =

> >  UNION
> > SELECT  i.content_id
> >      ,  i.image_name
> >      ,  i2.image_name
> >   FROM  image i
> >  RIGHT  JOIN  content c  ON (c.content_id =3D i.content_id)
> >         JOIN  image   i2 ON (i.content_id =3D i2.content_id)
> >  WHERE  i.image_type  =3D ?
> >    AND  i2.image_type =3D ? =

> > SQL
> > =

> > $new_source->name( \"(${sql})" );
> =

> I think
> =

>   $new_source->name( \"(${sql}) AS" );
> =

> would do the trick.
> =

> > Note the format of that me alias at the end.  Whenever I change that en=
d bit to 'AS me'
> > and run it in an sql-shell, it runs great.  So I was wondering if this =
is how DBIx::Class
> > is suppose to be formatting these ResultSource queries.  If so, how do =
I format the query
> > in my ResultSource file to get it to work like it should?
> =

> We don't AS in FROM clauses by default because some databases (older Orac=
les
> IIRC) don't support it.
> =

> -- =

>       Matt S Trout       Need help with your Catalyst or DBIx::Class proj=
ect?
>    Technical Director                    http://www.shadowcat.co.uk/catal=
yst/
>  Shadowcat Systems Ltd.  Want a managed development or deployment platfor=
m?
> http://chainsawblues.vox.com/            http://www.shadowcat.co.uk/serve=
rs/
> =

> _______________________________________________
> List: http://lists.scsys.co.uk/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.rawmod=
e.org

_________________________________________________________________
Connect and share in new ways with Windows Live.
http://www.windowslive.com/share.html?ocid=3DTXT_TAGHM_Wave2_sharelife_0120=
08
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20080212/b0b=
cf54e/attachment.htm