Request: Support for __JOIN(c1.fkey c2)__

[prev] [thread] [next] [Date index for 2005/05/04]

The __JOIN(c1 c2)__ placeholder is unpredictable if c1 has multiple has_a relationships to c2.  CDBI simply picks the first relationship it finds.  You should be able to explictly specify which relationship to use.

Example:

package Person;
use base 'Class::DBI';
__PACKAGE__->table('Person');
__PACKAGE__->columns(All => qw/ID FirstName LastName Father Mother/);
__PACKAGE__->has_a(Father => 'Person');
__PACKAGE__->has_a(Mother => 'Person');

Let's say you want to select a list of a Persons based on their Father's FirstName.  You should be able to do something like this:

SELECT __ESSENTIAL(p)__
FROM __TABLE(Person=p)__, __TABLE(Person=father)__
WHERE __JOIN(p.Father father)__ AND father.FirstName = 'Joe'

Here's a changed Class::DBI::transform_sql (based on version 0.96) that supports specifying which foreign key to use in a __JOIN__.  It it also supports specifying multiple joins (from left to right), so you could search based on a paternal grandfather's FirstName like so:

SELECT __ESSENTIAL(p)__
FROM __TABLE(Person=p)__, __TABLE(Person=father)__, __TABLE(Person=grandpa)__
WHERE __JOIN(p.Father father.Father grandpa)__ AND grandpa.FirstName = 'Joe'

sub transform_sql {
	my ($self, $sql, @args) = @_;

	my %cmap;
	my $expand_table = sub {
		my ($class, $alias) = split /=/, shift, 2;
		my $table = $class ? $class->table : $self->table;
		$cmap{ $alias || $table } = $class || ref $self || $self;
		($alias ||= "") &&= " AS $alias";
		return $table . $alias;
	};

	my $expand_join = sub {
		my $joins  = shift;
		my @table  = split /\s+/, $joins;
		my %tojoin = map { $table[$_] => $table[ $_ + 1 ] } 0 .. $#table - 1;
		my @sql;
		while (my ($t1, $t2) = each %tojoin) {
			my ($fcol, $col);
			($t1, $fcol) = split /\./, $t1;
			($t2) = split /\./, $t2;
			my ($c1, $c2) = map $cmap{$_}
				|| $self->_croak("Don't understand table '$_' in JOIN"), ($t1, $t2);

			if($fcol) {
				$col = $self->find_column($fcol) . ''
					|| $self->_croak("$c1 doesn't contain column $fcol");
				my $rel = $c1->meta_info('has_a')->{$col};
				$self->_croak("Can't join $c1 to $c2 using column $fcol")
					unless $rel && $rel->foreign_class eq $c2;
			} else {
				my $join_col = sub {
					my ($c1, $c2) = @_;
					my $meta = $c1->meta_info('has_a');
					my ($col) = grep $meta->{$_}->foreign_class eq $c2, keys %$meta;
					$col;
				};

				$col = $join_col->($c1 => $c2) || do {
					($c1, $c2) = ($c2, $c1);
					($t1, $t2) = ($t2, $t1);
					$join_col->($c1 => $c2);
				};
			}

			$self->_croak("Don't know how to join $c1 to $c2") unless $col;
			push @sql, sprintf " %s.%s = %s.%s ", $t1, $col, $t2,
				$c2->primary_column;
		}
		return join " AND ", @sql;
	};

	$sql =~ s/__TABLE\(?(.*?)\)?__/$expand_table->($1)/eg;
	$sql =~ s/__JOIN\((.*?)\)__/$expand_join->($1)/eg;
	$sql =~ s/__ESSENTIAL__/join ", ", $self->_essential/eg;
	$sql =~
		s/__ESSENTIAL\((.*?)\)__/join ", ", map "$1.$_", $self->_essential/eg;
	if ($sql =~ /__IDENTIFIER__/) {
		my $key_sql = join " AND ", map "$_=?", $self->primary_columns;
		$sql =~ s/__IDENTIFIER__/$key_sql/g;
	}
	return $self->SUPER::transform_sql($sql => @args);
}


    - Stepan