Skip Menu |
 

This queue is for tickets about the DBIx-Class CPAN distribution.

Report information
The Basics
Id: 26059
Status: resolved
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: mschwern [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: Unimportant
Broken in: (no value)
Fixed in: (no value)

Attachments


Subject: [PATCH] CDBICompat->retrieve_from_sql() must support LIMIT
Download (untitled) / with headers
text/plain 1.1k
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; +}
"resolved", at least once your patch set hits


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.