Rails SQL Query Builder... or Activerecord Query Builder

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.

CodeIgniter, Active Record or Query Builder?

Codeigniter Active record

CodeIgniter uses a modified version of the Active Record Database Pattern. This pattern allows information to be retrieved, inserted, and updated in your database with minimal scripting. In some cases only one or two lines of code are necessary to perform a database action. CodeIgniter does not require that each database table be its own class file. It instead provides a more simplified interface.

Refer this

Changelog version 3.0.0: (March 30, 2015)

Renamed the Active Record class to Query Builder to remove confusion with the Active Record design pattern.

ActiveRecord (CDbCriteria) vs QueryBuilder?

The concept of CDbCriteria is used when working with Yii's active record (AR) abstraction (which is usually all of the time). AR requires that you have created models for the various tables in your database.

Query builder a very different way to access the database; in effect it is a structured wrapper that allows you to programmatically construct an SQL query instead of just writing it out as a string (as an added bonus it also offers a degree of database abstraction as you mention).

In a typical application there would be little to no need to use query builder because AR already provides a great deal of functionality and it also offers the same degree of database abstraction.

In some cases you might want to run a very specific type of query that is not convenient or performant to issue through AR. You then have two options:

1. If the query is fixed or almost fixed then you can simply issue it through DAO; in fact the query builder documentation mentions that "if your queries are simple, it is easier and faster to directly write SQL statements".
2. If the query needs to be dynamically constructed then query builder becomes a good fit for the job.

So as you can see, query builder is not all that useful most of the time. Only if you want to write very customized and at the same time dynamically constructed queries does it make sense to use it.

The example feature that you mention can and should be implemented using AR.

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

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 converting to Rails Active Record Query Interface

Instead of converting it to an active record, you can use the find_by_sql method. Since your query is a bit complex.

You can use also use ActiveRecord::Base.connection, directly to fetch the records.

like this,

ActiveRecord::Base.connection.execute("your query") 

Rails: Convert a complex SQL query to Arel or ActiveRecord

While we can certainly build your query in Arel, after reviewing your SQL a bit it looks like it would actually be much cleaner to simply build this using the AR API instead.

The following should produce the exact query you are looking for (sans "newsletter_stories"."author_id" = "group_members"."id" because this is already implied by the join)

class Newsletter::Author < Application Record
  belongs_to :newsletter, inverse_of: :authors
  belongs_to :group_member, class_name: "GroupMember", inverse_of: :authorships
  scope :without_submissions_for, ->(newsletter_id) {
    group_members = GroupMember
         .select(:id)
         .joins(:stories)
         .where(newsletter_stories: {status: 'draft'})
         .where.not( 
           Newsletter::Story
            .select(1)
            .where(status: 'submitted')
            .where(Newsletter::Story.arel_table[:author_id].eq(GroupMember.arel_table[:id]))
            .arel.exists
         ).distinct
    where(discarded_at: nil, newsletter_id: newsletter_id, group_member_id: group_members)
  }
end 

---

Related Topics