Re: Class::DBI::AbstractSearch and order_by, limit...

[prev] [thread] [next] [Date index for 2004/09/01]

Michael Jensen wrote:

> I am trying to use Class::DBI::AbstractSearch and it works fine, but I 
> want to figure out how to use order_by and limit (using Rhesa's cool 
> modification). Is it even possible?

Order_by is very possible, since it's part of SQL::Abstract.

>        my @obj = Music::Artist->search_where(
>            name => [ 'Ozzy', 'Kelly' ],
>            status => { '!=', 'outdated' },
>              order_by ... ?
>        );

You have to put your search criteria in an anonymous hash, and the
order_by clause in a second:

         my @obj = Music::Artist->search_where(
		{
			name => [ 'Ozzy', 'Kelly' ],
			status => { '!=', 'outdated' },
		},
               	{
			order_by => 'name desc, status asc'
		}
         );


Unfortunately, LIMIT is not an option with search_where (nor is GROUP
BY, byt he way), since they are not supported by SQL::Abstract.

You could hack CDBI::AbstractSearch to add support yourself, and maybe
propose it as an update to the developer.

Rhesa



Here's a diff between the official and my version. You might prefer to
roll your own class...

--- AbstractSearch.pm   2004-09-01 17:05:41.601716800 +0200
+++ My\AbstractSearch.pm        2004-09-01 17:07:55.884806400 +0200
@@ -2,7 +2,7 @@

  use strict;
  use vars qw($VERSION @EXPORT);
-$VERSION = 0.05;
+$VERSION = 0.051;

  require Exporter;
  *import = \&Exporter::import;
@@ -15,6 +15,7 @@
      my $where = (ref $_[0]) ? $_[0]          : { @_ };
      my $attr  = (ref $_[0]) ? $_[1]          : undef;
      my $order = ($attr)     ? delete($attr->{order_by}) : undef;
+    my $limit = ($attr)     ? delete($attr->{limit}) : undef;

      # order is deprecated, but still backward compatible
      if ($attr && exists($attr->{order})) {
@@ -28,6 +29,7 @@
      my $sql = SQL::Abstract->new(%$attr);
      my($phrase, @bind) = $sql->where($where, $order);
      $phrase =~ s/^\s*WHERE\s*//i;
+    $phrase .= ' LIMIT ' . $limit if $limit =~ /\S/;
      return $class->retrieve_from_sql($phrase, @bind);
  }