Re: Feature request - Allow searching columns of related tables in search() and search_like()

[prev] [thread] [next] [Date index for 2005/01/11]

On Mon, Jan 10, 2005 at 11:12:13PM +0000, Tony Bowden wrote:
> IMO the key is just coming up with the syntax that links the various tables
> together.

If anyone wants to hack on it, the basics are below.

This allows you to say:

Star->many_many(movies => [ qw/Role Movie/ ]);

It's assumed that you'll be using the moniker of each class in the
query, and that the final module in the list is the one you're wanting
to end up with.

You can then do:

  my @movies = $fred->movies;
  my @by_role = $fred->movies("role.name" => "Billy");
  my @by_year = $fred->movies("year" => "2000");
  my @both = $fred->movies("role.name" => "Billy", "movie.year" => 2000);

This would obviously need to be polished up a lot for a proper version,
but this is the basic concept.

We need to find better syntax though for allowing for circumstances
where the above assumptions aren't true...

Tony


----

package Class::DBI::Relationship::ManyMany;

use strict;
use warnings;

use base 'Class::DBI::Relationship';

sub remap_arguments {
  my ($proto, $class, $accessor, @f_classes) = @_;
  $class->_require_class($_) foreach @f_classes;
  return ($class, $accessor, \@f_classes, {});
}

sub methods {
  my $self     = shift;
  return ($self->accessor => $self->_many_many_method);
}

sub _many_many_method {
  my $self       = shift;
  $self->_set_up_sql;
  my $meth = "sql_mm_" . $self->accessor;
  my @fc = @{ $self->foreign_class->[0] };
  return sub {
    my ($class, %args) = @_;
    my $interp = join " AND ", map "$_ = ?", keys %args;
    $interp &&= "AND $interp";
    my $sth = $class->$meth($interp);
    return $fc[-1]->sth_to_objects($sth, [$class->id, values %args]);
  }
}

sub _set_up_sql {
  my $self       = shift;
  my $class = $self->class;
  my $accessor = $self->accessor;
  my @f_classes = @{ $self->foreign_class->[0] };
  my $sql = sprintf qq{
    SELECT __ESSENTIAL(%s)__
      FROM %s
     WHERE __JOIN(%s)__
       AND %s = ?
     %%s
  }, $f_classes[-1]->moniker,
     join(", ", map sprintf("__TABLE(%s=%s)__", $_, $_->moniker),
       ($class, @f_classes)),
     join(" ", map $_->moniker, ($class, @f_classes)),
     $class->moniker;
  $class->set_sql("mm_$accessor" => $sql);
}

1;