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

[David Salgado]

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)

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.

David



On 1 March 2016 at 00:00, gvim <gvimrc at gmail.com> wrote:

> 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;
>
> }
>
>
>
>
>
>
>
>
>
>
>
> _______________________________________________
> Chat mailing list
> Chat at lists.lrug.org
> Archives: http://lists.lrug.org/pipermail/chat-lrug.org
> Manage your subscription: http://lists.lrug.org/options.cgi/chat-lrug.org
> List info: http://lists.lrug.org/listinfo.cgi/chat-lrug.org
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lrug.org/pipermail/chat-lrug.org/attachments/20160302/7726bd38/attachment-0002.html>