<div dir="ltr">TBH, if you've got a bit of nicely-optimised SQL that works well for your app. I'd recommend wrapping it in a class with a well-defined interface and just using ActiveRecord::Base.connection.execute(sql)<div><br></div><div>The ActiveRecord SQL-building code can be nice for some things, but in this case I suspect it's not going to give you much.</div><div><br></div><div>David</div><div><br></div><div><br></div></div><div class="gmail_extra"><br><div class="gmail_quote">On 1 March 2016 at 00:00, gvim <span dir="ltr"><<a href="mailto:gvimrc@gmail.com" target="_blank">gvimrc@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span class="">On 29/02/2016 10:01, Graham Ashton wrote:<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
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…<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
$sql .= q( FROM log_form l, activities a, packages p);<br>
$sql .= q(, vmaster m) unless $master eq 'e';<br>
$sql .= q( WHERE <a href="http://l.id" rel="noreferrer" target="_blank">l.id</a> = ?);<br>
$sql .= q( AND l.packageid = <a href="http://p.id" rel="noreferrer" target="_blank">p.id</a>);<br>
$sql .= q( AND l.activityid = <a href="http://a.id" rel="noreferrer" target="_blank">a.id</a>);<br>
$sql .= q( AND l.masterid = <a href="http://m.id" rel="noreferrer" target="_blank">m.id</a>) unless $master eq 'e';<br>
</blockquote>
<br>
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).<br>
</blockquote>
<br></span>
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.<br>
<br>
"log_row_sql" merely takes validated form data and enters a single row into the database according to some conditional logic.<span class=""><br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
I might have missed something in the code you posted, but there’s nothing in the query leaping out at me.<br>
<br>
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?<br>
<br>
</blockquote>
<br></span>
Here's the call:<br>
<br>
sub get_logrow {<br>
my ($c, $master_id, $log_id) = @_; # $c is a CGI::Application object<br>
my $sth = $c->dbh->prepare(log_row_sql($master_id)) or die "Couldn't prepare SQL statement: $!";<br>
$sth->execute($log_id) or die "Couldn't execute SQL statement: $!";<br>
my $row = $sth->fetchrow_hashref() or die "Couldn't retrieve database row: $!";<br>
$sth->finish();<br>
$row->{postarea} = undef if (defined $row->{postarea} and $row->{postarea} =~ /^IR\d{1,2}/);<br>
return $row;<div class="HOEnZb"><div class="h5"><br>
}<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
_______________________________________________<br>
Chat mailing list<br>
<a href="mailto:Chat@lists.lrug.org" target="_blank">Chat@lists.lrug.org</a><br>
Archives: <a href="http://lists.lrug.org/pipermail/chat-lrug.org" rel="noreferrer" target="_blank">http://lists.lrug.org/pipermail/chat-lrug.org</a><br>
Manage your subscription: <a href="http://lists.lrug.org/options.cgi/chat-lrug.org" rel="noreferrer" target="_blank">http://lists.lrug.org/options.cgi/chat-lrug.org</a><br>
List info: <a href="http://lists.lrug.org/listinfo.cgi/chat-lrug.org" rel="noreferrer" target="_blank">http://lists.lrug.org/listinfo.cgi/chat-lrug.org</a><br>
</div></div></blockquote></div><br></div>