> -----Original Message-----
> From: Zbigniew Lukasiak [mailto:[EMAIL PROTECTED]
> 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)
> <[EMAIL PROTECTED]> 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.
_______________________________________________
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/[email protected]
