Transform SQL

Class::DBI's transform_sql provides a number of useful transformations like __ESSENTIAL__ and __TABLE__ for keeping the SQL tidy in your set_sql statements. You can add more transformations by overriding the method in your CDBI-derived base class. For example, this will make __COLUMNS(Group)__ expand to the columns in that group.

sub transform_sql { my ($self, $sql, @args) = @_;
 * 1) Override transform_sql from Ima::DBI to provide some extra
 * 2) transformations

$sql =~ s/__COLUMNS\((.*?)\)__/join ", ", map "$_", $self->columns($1)/eg;

return $self->SUPER::transform_sql($sql => @args); }

Usage:

__PACKAGE__->columns(Primary => qw/id/); __PACKAGE__->columns(Basic => qw/foo bar baz/); __PACKAGE__->columns(Rare => qw/quux quuux quuuux quuuuux quuuuuux/); __PACKAGE__->set_sql(rare =>     "select __ESSENTIAL__, __COLUMNS(Rare)__ from __TABLE__ where id = ?");

__PACKAGE__->search_rare(1)->first;

This returns the object with id 1, with all of the quux columns loaded, but not foo, bar, or baz.

This can be useful when you know you're going to need a certain set of columns from a large number of rows, and you want to load them in one fell swoop, rather than fetching the objects and then letting LazyLoading bring in the columns as they're requested.

Note that this may not have the intended benefit if the objects being fetched are already in the LiveObjectsIndex: if an object is already in memory, the existing object will be returned without loading the data from the columns you just retrieved. Lazy loading will grab the columns from the database as needed, but any efficiency gain from loading them all at once will be lost.