[Dbix-class] Restricting Result Sets

[Howe, Tom (IT)]

> -----Original Message-----
> From: Zbigniew Lukasiak [mailto:zzbbyy at gmail.com]
> Sent: 15 November 2008 10:43
> To: DBIx::Class user and developer list
> Subject: Re: [Dbix-class] Restricting Result Sets
>
> On Sat, Nov 15, 2008 at 11:25 AM, Howe, Tom (IT)
> <Tom.Howe at morganstanley.com> wrote:
> > I've wanting something similar and ended up hacking
> dbix:class and added a subselect() method.
> >
> > Below is my previous post highlighting what I did
> > ----------------------------------------------------
> >
> > I'll give you a summary of what ive been doing and how I've
> tackled it.
> >
> > Essentially Ive been creating a reporting framework to
> allow users to create reports on various datasets held in
> sqlite databases.
> > The idea is that users can create reports on the raw data
> or based on existing reports.
>
> This is a very impressive goal!  I have just one question:
>
> >
> snip
> snip
>
> >  # apply search /paging
> >  # build whereclaus from cgi params
> >  my $whereClause = map { ... }  $self->cgi->params;
> >  $rs = $rs->subselect({ $whereClaus }.. { paging..} );   #
> search on resultset
>
> How this subselect is different from a normal search call?  I
> don't want to elaborate the obvious - but you can call search
> on any resultset - also one that is the output of another
> search - and it
> combines the where clauses in effect doing search on a search.   So
> what is the difference here?



As I understand it, if you have a table like

| col1 | col2 | col3 |

You can do
$rs2 = $rs->search( { col1 => { LIKE => 'foo'} } }, { select => [qw/col1, col2/] });

$rs3 = $rs2->search( {col2 => { LIKE => 'bar'} } }, { +select => [qw/col3/]);

This will combine the queries allowing you to restrict the resultset so you end up with SQL such as

"Select col1,col2,col3 from <table> where col1 like 'foo' and col2 like 'bar';"


But lets say you want to do the following...

$rs2 = $rs->search( { col1 => { LIKE => 'foo'} } }, { select => ['substr(col1,1,10) as blah', 'substr(col2,1,10) as blah2', 'count(*) as count)] }, group_by  => ['col3']);

$rs3 = $rs2->search( {blah1 => { LIKE => 'bar'}, count => '1' } }, { select => [qw/blah1 blah2 count/]);


It would try to create SQL something like:

Select blah1,blah2,count(*) as count  from <table> where col1 like 'foo' and blah1 like 'bar' and count=1 group by col3;

This would fail because
 - The second select would override the first select and blah1, blah2 don't exist in the resultsource.


I think the only way you can currently do this is by setting up special result_sources with custom sql. I didn't know about this when I started out with my application but it doesn't seem ideal for my purposes anyhow.


Using my subselect() feature I would write it as

$rs2 = $rs->search( { col1 => { LIKE => 'foo'} } }, { select => ['substr(col1,1,10) as blah', 'substr(col2,1,10) as blah2', 'count(*) as count)] }, group_by  => ['col3']);

$rs3 = $rs2->subselect( {blah1 => { LIKE => 'bar'}, count => '1' } }, { select => [qw/blah1 blah2 count/]);


The SQL for $rs2 gets rendered and placed into the 'from' attribute for $rs3 to generate a nested select that would look like

Select blah1,blah2,count(*) as count  from (
  select col1 as blah1, col2 as blah2, count(*) as count from  <table> where col1 like 'foo' group by col3;
 ) where blah1 like 'bar' and count =1;


I also added an asSQL() method and a 'compound' attribute so you can do Unions, excepts and intersections.

So you can do things like

$rs = $rs->search( {}, {} )
$rs = $rs->subselect( {}, {} )
$rs = $rs->subselect( {}, {} )

$rs2 = $rs->search( {}, {} )
$rs2 = $rs2->subselect( {}, {} )
$rs2 = $rs2->subselect( {}, {} )

$deltaSQL = $rs2->asSQL

$rs->search({} { compound => ' UNION $deltaSQL' };

Admitedly it's a bit hacky, but you can end up with  complex queries like the following:

Select x1,x2,x3, x4 from (
  select y1 as x1,y2 as x2,y3 as x3,y4 as x4 from (
     select z1 as y1,z2 as y2,z3 as y3,z4 as y5 from table where ... Group by ...
  ) where y1=.., y2=..   EXCEPT (select from ...)
) UNION
Select x1,x2,x3,x4 from (
   select ....
)

As far I as could see, my alternative was people creating these as views in the db,
but I don't want the users to have access to the db.


The tricky bit was getting the quoting right so that when you nest say:

select substr(col1,1,1) from table

It becomes

select "substr(col1,1,1)" from ( select substr(col1,1,1) from table );

But if you did

select substr(col1,1,1) as mystr from table

It needs to become

select mystr from ( select substr(col1,1,1) as mystr from table );



Tom
--------------------------------------------------------

NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error.