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

[Graham Ashton]

On Sun, 28 Feb 2016, at 09:12 PM, gvim wrote:

> I'm converting a Perl web application to Rails…

>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).

If my N+1 suspicion is right and you really need to pull back a set of results, you can improve on that nicely (from a performance point of view) by adjusting the `where()` part in this example so it identifies the right rows in the log_form table.

  LogForm.where(id: the_id).includes(:activities, :packages, :vmaster)

It’ll run 4 queries – one to find the matching log_form rows, and one more for each table in the `includes()` call.

If you need to pull back more than a handful of results, this approach works really well.

There’s also a find_by_sql method in ActiveRecord, and there’s absolutely nothing wrong with using it.

> It seems this is not a good fit for Active Record and maybe Rails in 
> general?

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?

-- 
Graham Ashton (Founder, Agile Planner)
https://www.agileplannerapp.com | @grahamashton | @agileplanner