| Subject: | [PATCH] CDBICompat->retrieve_from_sql() must support LIMIT |
The argument to CDBI->retrieve_from_sql() is pretty much "stick this on
the end of the SELECT statement", not just a WHERE clause. Thus things
like ORDER BY and LIMIT are kosher. Its right in the CDBI docs:
retrieve_from_sql
On occasions where you want to execute arbitrary SQL, but don't
want to
go to the trouble of setting up a constructor method, you can inline
the entire WHERE clause, and just get the objects back directly:
my @cds = Music::CD->retrieve_from_sql(qq{
artist = 'Ozzy Osbourne' AND
title like "%Crazy" AND
year <= 1986
ORDER BY year
LIMIT 2,3
});
The CDBICompat layer throws the argument to SQL::Abstract as the
condition. Unfortunately SQL::Abstract puts the condition in parens so
you wind up with things like "WHERE ( foo = 42 LIMIT 2 )".
SQL::Abstract has a special case for ORDER BY but not LIMIT.
The patch is a quick hack to search_literal() to look for LIMIT, pull it
out of the where clause and use it to set $rs->rows(). A proper fix
would be to bypass SQL::Abstract and tack the exact given SQL onto the
end of the normal SQL retrieve statement, but I don't know how to do that.
| Subject: | retrieve_from_sql_with_limit.patch |
=== lib/DBIx/Class/CDBICompat/Retrieve.pm
==================================================================
--- lib/DBIx/Class/CDBICompat/Retrieve.pm (revision 27868)
+++ lib/DBIx/Class/CDBICompat/Retrieve.pm (local)
@@ -47,8 +47,14 @@
sub retrieve_from_sql {
my ($class, $cond, @rest) = @_;
+
$cond =~ s/^\s*WHERE//i;
- $class->search_literal($cond, @rest);
+
+ if( $cond =~ s/\bLIMIT (\d+)\s*$//i ) {
+ push @rest, { rows => $1 };
+ }
+
+ return $class->search_literal($cond, @rest);
}
sub retrieve_all { shift->search }
=== t/cdbi-t/retrieve_from_sql_with_limit.t
==================================================================
--- t/cdbi-t/retrieve_from_sql_with_limit.t (revision 27868)
+++ t/cdbi-t/retrieve_from_sql_with_limit.t (local)
@@ -0,0 +1,27 @@
+#!/usr/bin/perl -w
+
+use strict;
+use Test::More;
+
+BEGIN {
+ eval "use DBIx::Class::CDBICompat;";
+ plan $@ ? (skip_all => "Class::Trigger and DBIx::ContextualFetch required: $@")
+ : (tests=> 3);
+}
+
+INIT {
+ use lib 't/testlib';
+ use Film;
+}
+
+for my $title ("Bad Taste", "Braindead", "Forgotten Silver") {
+ Film->insert({ Title => $title, Director => 'Peter Jackson' });
+}
+
+Film->insert({ Title => "Transformers", Director => "Michael Bay"});
+
+{
+ my @films = Film->retrieve_from_sql(qq[director = "Peter Jackson" LIMIT 2]);
+ is @films, 2, "retrieve_from_sql with LIMIT";
+ is( $_->director, "Peter Jackson" ) for @films;
+}