Using joins

From ClassDBI

Based on a discussion from the mailing list, there appear to be a couple of ways to handle joins using CDBI:

• Use set_sql to create a method that uses custom sql
• Use DBI directly

A simple case of using set_sql to create joins is provided in the documentation.

However, it is possible to do more advanced joins, such as creating dynamic WHERE statements based on user input such as a web-based search form. The code to setup this method would look like (untested):

   $class->set_sql(my_query => <<"");
   SELECT __ESSENTIAL__
   FROM   __TABLE(Class::One=c1)__,
          __TABLE(Class::Two=c2)__
   WHERE  __JOIN(c1 c2)__
   AND    %s

The above call to set_sql will generate a search_my_query function as described in the documentation. However, you can create an alternate method which can define how the WHERE clause is built:

 sub my_join_search {
   my ($class, $args) = @_;
   my $where_clause = join " AND ", map "$_ LIKE ?", keys %$args;
   return $class->sth_to_objects(
       $class->sql_my_query($where_clause), values %args
       );
 }

Tony made the following remarks regarding the above solution:

To be really robust you'll need to cope with overriden accessor names etc, and you can abstract away that LIKE to be more generic so you don't have to repeat yourself for ILIKE or EQUALS or whatever. But that should give the gist. It's how search() works in Class::DBI itself, and you can always look at it for some more details on how to abstract this further. And, as I said earlier, I should really abstract more of this out further so that writing these yourself is even easier...

---

Using SQL::Abstract in the my_join_search() helps a lot in abstracting the details:

 sub my_join_search {
   my ($class, @args) = @_;
   my $sql = SQL::Abstract->new();
   my ($where, @bind) = $sql->where(@args);
   return $class->sth_to_objects(
       $class->sql_my_query($where), \@bind
       );
 }

-- RhesaRozendaal

UPDATE: If using the set_sql from above, you need to add the following line before the "return $class..." to avoid SQL syntax errors:

    $where =~ s/^\s*WHERE//;

-- WilliamMcKee

---

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.

-- RandalSchwartz

---

If you like the CDBI::AbstractSearch interface (and who wouldn't?) but need to search (or sort) on fields from joined tables (and who doesn't?), you can use the Class::DBI::Plugin::DeepAbstractSearch plugin. It automatically generates the necessary joins when building the query. It uses the SQL::Abstract syntax, but you can specified "dotted" fields:

 my @cds = Music::CD->deep_search_where( { 'artist.name' => $artist_name } );
 my @tracks = Music::Track->deep_search_where(
   {
     'cd.artist.name' => $artist_name,
     'cd.year' => { '>=', $year }
     'track_position' => { '<=', $num_tracks }
   },
   {
     order_by => 'cd.artist.name, cd.year DESC, track_position'
   }
 );

When processing form input, your code usually looks like this:

 my $cgi = ...;
 my $where = {};
 $where->{ 'cd.artist.name' } => $cgi->param('artist_name')
   if $cgi->param('artist_name');
 $where->{ 'cd.year' } => { '>=', $cgi->param('year') }
   if $cgi->param('year');
 $where->{ 'track_position' } => { '<=', $cgi->param('track_position') }
   if $cgi->param('track_position');
 $where->{ 'cd.title' } => { -like => '%' . $cgi->param('title') . '%' }
   if $cgi->param('title');

 my @tracks = Music::Track->deep_search_where ($where, { order_by => ... });

-- StepanRiha

Rather than creating complex joins, I have been experimenting with postgresql views. I then create a Class::DBI class for the view as if it were a normal table.

I am not sure if Class::DBI is intended to work with views in this way but so-far it seems to work ok with the exception of Lazy Population for column names. I have had to declare all the views columns as being Essential to ensure that it populates the object correctly.

-- IcyDee