[LRUG] Conditionally built SQL suitable for Active Record/Rails?

[gvim]

On 29/02/2016 10:01, Graham Ashton wrote:

>  From the name of the function ("log_row_sql”) I’m wondering whether that query is run once to build a row of output in a multi-row table? If so, it’s an N+1 query waiting to be optimised…
>
>>        $sql .= q( FROM log_form l, activities a, packages p);
>>        $sql .= q(, vmaster m) unless $master eq 'e';
>>        $sql .= q( WHERE l.id = ?);
>>        $sql .= q( AND l.packageid = p.id);
>>        $sql .= q( AND l.activityid = a.id);
>>        $sql .= q( AND l.masterid = m.id) unless $master eq 'e';
>
> That looks like you’d need 4 ActiveRecord models – LogForm, Activity, Package and VMaster. A LogForm “belongs to" activity, package and vmaster (and they in turn probably "have many” – or possibly just one – activities).

The database works fine within the Perl app as far as foreign keys and 
normalisation are concerned. I'm just wondering whether shoe-horning the 
SQL into Active Record is going to be more pain than it's worth.

"log_row_sql" merely takes validated form data and enters a single row 
into the database according to some conditional logic.

>
> I might have missed something in the code you posted, but there’s nothing in the query leaping out at me.
>
> If my suggestion doesn’t look great, can you share the context that this function is called in? What does the app do with the results?
>

Here's the call:

sub get_logrow {
   my ($c, $master_id, $log_id) = @_;    # $c is a CGI::Application object
   my $sth = $c->dbh->prepare(log_row_sql($master_id)) or die "Couldn't 
prepare SQL statement: $!";
   $sth->execute($log_id) or die "Couldn't execute SQL statement: $!";
   my $row = $sth->fetchrow_hashref() or die "Couldn't retrieve database 
row: $!";
   $sth->finish();
   $row->{postarea} = undef if (defined $row->{postarea} and 
$row->{postarea} =~ /^IR\d{1,2}/);
   return $row;
}