Re: [Openinteract-help] SPOPS: fetch with mult attribute limits?

[Chris W. <ch...@cw...>]

Phil R Lawrence wrote:
> My question is about supported usage syntax.  How does one fetch records 
> based on *mutiple* criteria?  I saw this example for fetching based on 
> one criterion:
> 
>   1: my $publisher = My::Publisher->fetch( $pub_id );
>   2: my $books = $publisher->book;
>   3: print "Books published by $publisher->{name}:\n";
>   4: foreach my $book ( @{ $books } ) {
>   5:    print "  $book->{name}\n";
>   6: }
> 
> But I would like to select books based on multiple attribute limits...
> Ex:
>   my $books = My::Book->fetch(
>     publisher => My::Publisher->fetch( name => $pub_name ),
>     pages => ['<' => $pages],
>   );

This would be:

   my $books = My::Book->fetch_group({
                     where => 'name = ? AND pages < ?',
                     value => [ $pub_name, $pages ] });

This uses a DBI/SQL syntax, but SQL isn't very difficult 
(particularly for stuff like this) and most people are familiar 
with it. Besides, who wants to invent another query language? :-)

>   print "Books pub'd by $pub_name with < $pages pages.\n"
>   foreach my $book ( @{ $books } ) {
>     print "  $book->{name}\n";
>   }

This would be the same.

> BIG PICTURE:
> For ad hoc reporting, I ultimately want to offer a series of WWW forms 
> that:
>   1. prompt user to pick 1 of the available objects
>   2. prompt user to choose fields (attributes) of that object
>      for SELECTion
>   3. prompt user with all the available limits (attributes)
>      for that object.  User will set the values with which the
>      object population will be limited.
> 
> Assuming I can programaticly interrogate the schema such that #1-3 are 
> possible, I will need to generate method calls to get the result.  Thus 
> my question above.

Yep, no problem at all. I do this all the time, typically by 
treating the WHERE clause as a list along with all the associated 
values, then doing a join at the end to make the clause into a 
string. For instance, to take a simple case:

  my @search_fields = qw( publisher author publish_year );
  my @where  = ();
  my @values = ();
  foreach my $field ( @search_fields ) {
     # this could be CGI, Apache, whatever
      my $value = get_param_value( $field );
      next unless ( $value );
      push @where ' $field = ? ';
      push @values, $value;
  }
  my $books = My::Book->fetch_group({
                    where => join( ' AND ', @where ),
                    value => \@values });
  ...

Hope this makes sense.

Chris

-- 
Chris Winters (ch...@cw...)
Building enterprise-capable snack solutions since 1988.