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

[Glenn @ Ruby Pond Ltd]

I've found in cases where it's easier for me to be building the SQL myself
Sequel (https://github.com/jeremyevans/sequel) is easier to wrestle than
AR. The ability to build up SQL commands and pass them around like closures
to be evaluated later or used as subqueries become particularly helpful.

On Mon, Feb 29, 2016 at 9:02 PM Graham Ashton <graham at effectif.com> wrote:

> 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
> _______________________________________________
> 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/020ac345/attachment.html>