Sql Query Builder in Rails

Rails SQL query builder... Or ActiveRecord query builder

You need the squeel gem. It extends AR with blocks and makes very complicated queries with ease.

Just few features:

# not_in == cool! )
Product.where{id.not_in LineItem.select{product_id}}
# SELECT "products".* FROM "products" WHERE "products"."id" NOT IN 
# (SELECT "line_items"."product_id" FROM "line_items" )

# outer joins on pure Ruby:
LineItem.joins{product.outer}
# LineItem Load (0.0ms)  SELECT "line_items".* FROM "line_items" 
# LEFT OUTER JOIN "products" ON "products"."id" = "line_items"."product_id"

# calcs, aliasing:
Product.select{[avg(price).as(middle)]}
# SELECT avg("products"."price") AS middle FROM "products"

# comparison
Product.where{id != 100500}
Product.where{price<10}

# logical OR
Product.where{(price<10) | (title.like '%rails%')}
# SELECT "products".* FROM "products" WHERE (("products"."price" < 10 OR
# "products"."title" LIKE '%rails%'))

# xxx_any feature (also available xxx_all)
Product.where{title.like_any %w[%ruby% %rails%]}
# SELECT "products".* FROM "products" WHERE (("products"."title" LIKE '%ruby%' OR 
# "products"."title" LIKE '%rails%'))    

Note the using blocks: {...} here aren't hashes. Also note the absence of symbols.

If you decide to pick it, read the section that starts with "This carries with it an important implication"

Building queries dynamically in rails

You can create a SQL query based on your hash. The most generic approach is raw SQL, which can be executed by ActiveRecord.

Here is some concept code that should give you the right idea:

query_select = "select * from "
query_where = ""
tables = [] # for selecting from all tables
hash.each do |table, values|
  table_name = table.constantize.table_name
  tables << table_name
  values.each do |q|
    query_where += " AND " unless query_string.empty?
    query_where += "'#{ActiveRecord::Base.connection.quote(table_name)}'."
    query_where += "'#{ActiveRecord::Base.connection.quote(q[fieldName)}'"
    if q[:operator] == "starts with" # this should be done with an appropriate method
      query_where += " LIKE '#{ActiveRecord::Base.connection.quote(q[val)}%'"
    end
  end
end
query_tables = tables.join(", ")
raw_query = query_select + query_tables + " where " + query_where 
result = ActiveRecord::Base.connection.execute(raw_query)
result.to_h # not required, but raw results are probably easier to handle as a hash

What this does:

• query_select specifies what information you want in the result
• query_where builds all the search conditions and escapes input to prevent SQL injections
• query_tables is a list of all the tables you need to search
• table_name = table.constantize.table_name will give you the SQL table_name as used by the model
• raw_query is the actual combined sql query from the parts above
• ActiveRecord::Base.connection.execute(raw_query) executes the sql on the database

Make sure to put any user submitted input in quotes and escape it properly to prevent SQL injections.

For your example the created query will look like this:

select * from companies, categories where 'companies'.'name' LIKE 'a%' AND 'companies'.'hq_city' = 'karachi' AND 'categories'.'name' NOT LIKE '%ECommerce%'

This approach might need additional logic for joining tables that are related.
In your case, if company and category have an association, you have to add something like this to the query_where

"AND 'company'.'category_id' = 'categories'.'id'"

Easy approach: You can create a Hash for all pairs of models/tables that can be queried and store the appropriate join condition there. This Hash shouldn't be too complex even for a medium-sized project.

Hard approach: This can be done automatically, if you have has_many, has_one and belongs_to properly defined in your models. You can get the associations of a model using reflect_on_all_associations. Implement a Breath-First-Search or Depth-First Search algorithm and start with any model and search for matching associations to other models from your json input. Start new BFS/DFS runs until there are no unvisited models from the json input left. From the found information, you can derive all join conditions and then add them as expressions in the where clause of the raw sql approach as explained above. Even more complex, but also doable would be reading the database schema and using a similar approach as defined here by looking for foreign keys.

Using associations: If all of them are associated with has_many / has_one, you can handle the joins with ActiveRecord by using the joins method with inject on the "most significant" model like this:

base_model = "Company".constantize
assocations = [:categories]  # and so on
result = assocations.inject(base_model) { |model, assoc| model.joins(assoc) }.where(query_where)

What this does:

• it passes the base_model as starting input to Enumerable.inject, which will repeatedly call input.send(:joins, :assoc) (for my example this would do Company.send(:joins, :categories) which is equivalent to `Company.categories
• on the combined join, it executes the where conditions (constructed as described above)

Disclaimer The exact syntax you need might vary based on the SQL implementation you use.

Rails SQL: Creating a query dynamically

You need to pass sql_params with * (known as splat operator) i.e.

results = Model.where(conditions.join(' AND '), *sql_params)

SQL query builder in rails

Check out arel. A fork of this project is now used in Rails 3 to help with ORM agnosticism.

Dynamic query builder stack too deep for Arel query Rails

my solution finally is:

my_big_set = Set.new(big_array)
subquery = '(condition = ? and condition_two = ?)'
query = Array.new(my_big_set, subquery).join(' OR ')
query_values = my_big_set.map do |values_to_check|
   [values_to_check[:first], values_to_check[:two]]
end.flatten

where(query, *query_values).update_all(field_to_update: true)

that way, we construct:

1. the SQL query
2. the values to pass to where()
3. we still use active record where() in order to be protected from injection etc...

And this fixes the limit!

Rails: Run raw sql query returning extra info and build models

Are you trying to do something like this:

ActiveRecord::Base.connection.execute("sql here").map do |hash|
  new_info = harvest_info(hash)
  Comment.new(hash.merge(new_info)) if some_requirement?
end

visual sql query generator for ruby on rails

This would need a lot of TLC to get to be like the MS Access Query, but you could use the Ransack gem to accomplish the queries and nested associations.

http://railscasts.com/episodes/370-ransack

How can I see the SQL that will be generated by a given ActiveRecord query in Ruby on Rails

When last I tried to do this there was no official way to do it. I resorted to using the function that find and its friends use to generate their queries directly. It is private API so there is a huge risk that Rails 3 will totally break it, but for debugging, it is an ok solution.

The method is construct_finder_sql(options) (lib/active_record/base.rb:1681) you will have to use send because it is private.

Edit: construct_finder_sql was removed in Rails 5.1.0.beta1.

---

Related Topics