There was a discussion at IRC about the need for -in to accept a
subquery. Like this:
my $cdrs2 = $cdrs->search({
artist => {
-in => $art_rs->get_column( 'artistid' )->as_query,
},
});
I am attaching a simple patch to
http://dev.catalyst.perl.org/repos/bast/SQL-Abstract/1.x/branches/1.50_RC
that enables this (with one test) plus a test for DBIC (svn diff
againsthttp://dev.catalyst.perl.org/repos/bast/DBIx-Class/0.08/branches/subquery).
Cheers,
Zbigniew
On Fri, Feb 13, 2009 at 3:07 PM, Peter Rabbitson <[email protected]> wrote:
> Zbigniew Lukasiak wrote:
>> Hi,
>>
>> The 0.08011 release of DBIC, introduced for compatibility with the new
>> SQL::Abstract, is now on CPAN. What is now holding the SQL::Abstract
>> release?
>>
>
> The first release candidate is already on CPAN.
> http://search.cpan.org/~mstrout/SQL-Abstract-1.49_01/
>
> Get it while it's hot and let us know how does it fare.
>
>
> _______________________________________________
> 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]
>
--
Zbigniew Lukasiak
http://brudnopis.blogspot.com/
http://perlalchemy.blogspot.com/
Index: t/07subqueries.t
===================================================================
--- t/07subqueries.t (revision 5470)
+++ t/07subqueries.t (working copy)
@@ -25,6 +25,16 @@
bind => [100, "foo%", 1234],
};
+$where = {
+ foo => 1234,
+ bar => { -in => \[ $sub_stmt => @sub_bind] },
+ };
+push @tests, {
+ where => $where,
+ stmt => " WHERE ( bar IN (SELECT c1 FROM t1 WHERE c2 < ? AND c3 LIKE ?) AND foo = ? )",
+ bind => [100, "foo%", 1234],
+};
+
#2
($sub_stmt, @sub_bind)
= $sql->select("t1", "c1", {c2 => {"<" => 100},
Index: lib/SQL/Abstract.pm
===================================================================
--- lib/SQL/Abstract.pm (revision 5470)
+++ lib/SQL/Abstract.pm (working copy)
@@ -694,17 +694,23 @@
sub _where_field_IN {
my ($self, $k, $op, $vals) = @_;
+ my ($label) = $self->_convert($self->_quote($k));
+ my ($placeholder) = $self->_convert('?');
+ my $and = $self->_sqlcase('and');
+ $op = $self->_sqlcase($op);
+
+ # accepting \[ $subquery, @bind ]
+ if( ref( $vals ) eq 'REF' && ref( $$vals ) eq 'ARRAY' ){
+ my( $stmt, @bind ) = @$$vals;
+ return ( "$label $op ( $stmt )", @bind );
+ }
+
# backwards compatibility : if scalar, force into an arrayref
$vals = [$vals] if defined $vals && ! ref $vals;
ref $vals eq 'ARRAY'
or puke "special op 'in' requires an arrayref";
- my ($label) = $self->_convert($self->_quote($k));
- my ($placeholder) = $self->_convert('?');
- my $and = $self->_sqlcase('and');
- $op = $self->_sqlcase($op);
-
if (@$vals) { # nonempty list
my $placeholders = join ", ", (($placeholder) x @$vals);
my $sql = "$label $op ( $placeholders )";
Index: t/resultset/as_query.t
===================================================================
--- t/resultset/as_query.t (revision 5470)
+++ t/resultset/as_query.t (working copy)
@@ -10,7 +10,7 @@
use DBICTest;
use DBIC::SqlMakerTest;
-plan tests => 5;
+plan tests => 6;
my $schema = DBICTest->init_schema();
my $art_rs = $schema->resultset('Artist');
@@ -80,4 +80,12 @@
warn Dumper $cdrs2->as_sql;
}
+{
+ my $cdrs2 = $cdrs->search({
+ artist => {
+ -in => $art_rs->get_column( 'artistid' )->as_query,
+ },
+ });
+ ok( $cdrs2->as_query, '-in with a subquery' );
+}
__END__
_______________________________________________
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]
