Databases
StackOverflow2013
Postsdbo
POWhy use SQL builders? Arel v. Sequel v. T-SQL
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy use SQL builders? Arel v. Sequel v. T-SQL
    text
    copied!<p>I'm trying to understand the benefits of building SQL via an object-oriented builder DSL vs. parameterizing a raw SQL string. After researching/implementing the same query three ways, I notice that the raw SQL is by far the easiest to read. This begs the question, "why jump through a hoop?" Why not just declare and use raw SQL?</p> <p>Here's what I've come up:</p> <p>First, I guess it makes the SQL more portable as it could then be utilized by any DB with an adapter. I guess this is the biggie, right? Still, isn't most T-SQL intelligible to most databases?</p> <p>Second, it provides a query object that can be reused--as the basis for other queries, named-scope chaining, etc. </p> <p>What's the main return on investment you realize by building your SQL instead of declaring it?</p> <pre><code>def instances_of_sql(ttype_id) #raw sql ttype_id = get(ttype_id).try(:id) ti = get('tmdm:type-instance') inst = get('tmdm:instance') type = get('tmdm:type') self.class.send :sanitize_sql, [%{ SELECT t.* FROM associations a JOIN roles type ON type.association_id = a.id AND type.ttype_id = ? JOIN roles inst ON inst.association_id = a.id AND inst.ttype_id = ? JOIN topics t ON t.id = inst.topic_id WHERE a.topic_map_id IN (?) AND a.ttype_id = ? AND type.topic_id = ? }, type.id, inst.id, self.ids, ti.id, ttype_id] end def instances_of_sql(ttype_id) #sequel ttype_id = get(ttype_id).try(:id) ti = get('tmdm:type-instance') ir = get('tmdm:instance') tr = get('tmdm:type') DB.from(:associations.as(:a)). join(:roles.as(:tr), :tr__association_id =&gt; :a__id, :tr__ttype_id =&gt; tr[:id]). join(:roles.as(:ir), :ir__association_id =&gt; :a__id, :ir__ttype_id =&gt; ir[:id]). join(:topics.as(:t), :t__id =&gt; :ir__topic_id). where(:a__topic_map_id =&gt; self.ids). where(:a__ttype_id =&gt; ti[:id]). where(:tr__topic_id =&gt; ttype_id). select(:t.*).sql end def instances_of_sql(ttype_id) #arel ttype_id = get(ttype_id).try(:id) ti = get('tmdm:type-instance') inst = get('tmdm:instance') type = get('tmdm:type') #tables t = Topic.arel_table a = Association.arel_table tr = Role.arel_table ir = tr.alias a. join(tr).on(tr[:association_id].eq(a[:id]),tr[:ttype_id].eq(type[:id])). join(ir).on(ir[:association_id].eq(a[:id]),ir[:ttype_id].eq(inst[:id])). join(t).on(t[:id].eq(ir[:topic_id])). where(a[:topic_map_id].in(self.ids)). where(a[:ttype_id].eq(ti[:id])). where(tr[:topic_id].eq(ttype_id)). project('topics.*').to_sql end </code></pre> <p><em>I totally appreciate named scopes and see how chaining them can be beneficial. I'm not worried about accessing related records via a model. I'm purely talking about building a complex query.</em></p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload