creating a virtual view

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

Just posted to http://www.class-dbi.com/cgi-bin/wiki/index.cgi?UsingJoins

    Building on the above methods, I had class A 1-to-many B many-to-1
    C many-to-1 D, and I wanted to build a "virtual view" to find all
    B's where conditions were true in both A and D.  So, I created a
    set_sql in B's class:

      __PACKAGE__->set_sql(abcd_view => q{
      SELECT __ESSENTIAL__
      FROM a NATURAL JOIN b NATURAL JOIN c NATURAL JOIN d
      %s
      });

    And then code to call it in B:

      sub abcd_where {
        my $class = shift;
        my $sql = SQL::Abstract->new;
        my ($where, @bind) = $sql->where(@_);
        # untaint $where
        $where =~ /(.*)/s or die;
        $where = $1;
        return $class->sth_to_objects($class->sql_abcd_view($where), \@bind);
      }

    And now I can say things like this in my main code.

      my @good_b = B->abcd_where({'a.foo' => 15, 'd.bar' => [3, 5, 9]});

    Rock on.
        -- 
        Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@xxxxxxxxxx.xxx> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!