Baby Moose Link

Its link day here in the Moose-Pen

Well seeing as my elements/fields and conditions/Where are working quite well I think it is time to move on to the next attribute in Accessor.pm Links.

In SQL joins are the same as Database::Accessor Links so given this SQL;

SELECT people.first_name, people.last_name, people.id, address.street FROM people LEFT JOIN address ON people.id = address.user_id WHERE people.first_name = ?
the

LEFT JOIN address ON people.id = address.user_id
is represented by;

    links => [{type=>'LEFT',
               to  =>{name=>'address'},
               predicates=>[{left=>{name=>'id'},
                            right=>{name=>'user_id',
                                    view=>'address'}
                           }]}]
So much the same as we have seen with the 'where/condtion' clause. The first thing to do is a test and I have created a new test case for these called '40_links.t' and taking a lesson from yesterday's post I am going to use that 'sql_param_ok' sub from Test::Utils and the same iteration pattern as before. No need therefore to show you the whole test case. Here is just the start of the test array-ref I will iterate though.

my $tests = [{
    index=>0,
    key  =>'links',
    links =>{type=>'LEFT',
               to  =>'address',
               predicates=>[{left=>{name=>'id'},
                            right=>{name=>'user_id',
                                    view=>'address'}
                           }]},
    caption => "Left Link with 1 param",
    sql     => "SELECT people.first_name, people.last_name, people.id, address.street FROM people LEFT JOIN address ON people.id = address.user_id WHERE people.first_name = ?",
    params  => ['test1']
}];
The first thing I have to do is make sure that Accessor.pm will check all those 'Elements' in the link have the correct view and I will just do the same as I did for 'conditions'

…
--                conditions         => [@{$self->conditions},@{$self->dynamic_conditions}],
++                conditions         => $self->check_predicates([@{$self->conditions},@{$self->dynamic_conditions}]),                links              => $self->check_predicates([@{$self->links},@{$self->dynamic_links}]),
                gathers            => ($action eq Database::Accessor::Constants::RETRIEVE) ? [@{ $self->gathers },@{ $self->dynamic_gathers }] : [],
…
and I think that is all I need there. Next in Driver::DBI I will need some code to add in a join clause which I will call like this;

...
    $sql .= $self->_where_clause();
++    $sql .= $self->_join_clause()
…
that I will call in the execute sub.

Looking at the code I think I will first have to change my '_where_clause' and '_predicate_clause' subs a little as the format of a SQL JOIN is a little different than a wWHERE. I also have to keep in mind that unlike a 'WHERE' I could have any number of 'JOIN' clauses on an SQL.

After a few mins of playing about here is the first crack at it;

sub _where_clause {
    my $self = shift;
    return ""
      unless ( $self->condition_count );
    return join(" ",
                Database::Accessor::Driver::DBI::SQL::WHERE,
                " ",
                $self->_predicate_clause( Database::Accessor::Driver::DBI::SQL::WHERE,
                $self->conditions ));
}

sub _join_clause {
    my $self = shift;
    return ""
      unless ( $self->link_count );
    my @join_clauses = ();
    foreach my $join (@{$self->links()}){
       my $clause = join(" "
                         ,$join->type,
                         ," "
                         ,$self->_view_sql($join->to)
                         ," "
                         ,Database::Accessor::Driver::DBI::SQL::ON
                         ," "
                         , $self->_predicate_clause( 
                             Database::Accessor::Driver::DBI::SQL::JOIN,
                             $join->predicates() )
                          );
                         
       push(@join_clauses,$clause );
    }
    return “ “
              .join(" "
               ,@join_clauses);
                
}

sub _predicate_clause {
    my $self = shift;
    my ( $clause_type, $conditions ) = @_;
    my $predicate_clause = "";

    foreach my $condition ( @{$conditions} ) {
        foreach my $predicate ( @{ $condition->{predicates} } ) {
          $predicate_clause .= $self->_predicate_sql($predicate);
        }
    }
    $self->da_warn( "_predicate_clause",
        $clause_type . " clause='$predicate_clause'" )
      if $self->da_warning() >= 5;
    return $predicate_clause;
}
and the result of the first test was this SQL

SELECT people.first_name, people.last_name, people.id, address.street FROM peopleWHERE   people.first_name = ?LEFT   people   ON  
so a little fix needed here and there; First off I mistakenly put my WHERE before my JOIN;

--  $sql .= $self->_where_clause();
    $sql .= $self->_join_clause();
++  $sql .= $self->_where_clause();
    
and that gives me

… peopleLEFT   people   ON   WHERE   people.first_name = ?'
so now to fix that JOIN clause;

   my $clause = join(" "
                         ,$join->type,
                         ,Database::Accessor::Driver::DBI::SQL::JOIN
                         ,$self->_view_sql($join->to)
                         ,Database::Accessor::Driver::DBI::SQL::ON
                         , $self->_predicate_clause( 
                             Database::Accessor::Driver::DBI::SQL::JOIN,
                             $join->predicates() )
                          );
which gets me;

... LEFT JOIN people   ON WHERE   people.first_name = ?
so there is a problem in the '$self->_view_sql($join->to)' call. Now that sub is working correctly;

sub _view_sql {
  my $self = shift;
  my $view = $self->view()->name;
  $view = join(" ",
               $self->view()->name,
               $self->view()->alias)
    if $self->view()->alias();
  return $view;
}
but that is not what I want as the above gives me the SQL for the 'View' of the Driver::DBI not the sql for the 'to' attribute of the Join which is also a View class. I guess I will replace that sub with;

sub _table_sql {
  my $self = shift;
  my ($view) = @_;
  
  my $sql = $view->name;
  
  $sql = join(" ",
               $view->name,
               $view->alias)
    if $view->alias();
  return $sql;
}
and make the appropriate changes elsewhere in the code to swap out '_view_sql' and that fixes that as I now get;

LEFT JOIN address   ON WHERE   people.first_name = ?
but still no predicate in between the 'ON' and 'WHERE'. Eventually I found it in the '_predicate_clause' sub two things where wrong first I was using the very bad form

 @{ $condition->{predicates} }
as that will fail silently if there are no predicates. It should have been

 @{ $condition->predicates }
and that gave me an error that I fixed by accounting for the fact that a $condition can be a 'Predicate' or a 'Condition' class.

...
foreach my $condition ( @{$conditions} ) {
       if (ref($condition) eq 'Database::Accessor::Condition'){
        foreach my $predicate ( @{ $condition->predicates } ) {
          $predicate_clause .= $self->_predicate_sql($predicate);
        }
      }
      else {
        $predicate_clause .= $self->_predicate_sql($condition);
        
       }
    }
and that gives me;

FROM people LEFT JOIN address   ON people.id = address.user_idWHERE   people.first_name = ?
now just a little clean up on the spacing which is not really worth adding in here I finally get

ok 1 - Left Link with 1 param SQL correct
ok 2 - Left Link with 1 param params correct
So that was mostly painless;


DSC08952a.jpg

Leave a comment

About byterock

user-pic Long time Perl guy, a few CPAN mods allot of work on DBD::Oracle and a few YAPC presentations