byterock

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;