Ruby Activerecord and SQL Tuple Support

Rails3: SQL execution with hash substitution like .where()

query = ActiveRecord::Base.connection.raw_connection.prepare("INSERT INTO users (name) VALUES(:name)")
query.execute(:name => 'test_name')
query.close

ActiveRecord execute raw sql not returning any results?

Mysql2::Result or PG::Result object has a each method to iterate through results

ActiveRecord::Base.connection.execute("SELECT sometable.* from sometable limit 10").each |row|
  p row
end

Note: You cannot access the results like array. Need to use each method

 array = ActiveRecord::Base.connection.execute("SELECT sometable.* from sometable limit 10")
 array[0] # This will not work

Try select_all if you want results in Array

ActiveRecord::Base.connection.select_all "SELECT sometable.* from sometable limit 10"

ActiveRecord OR query Hash notation

There are 5 options that could be considered as implementations of «Hash notation» (the last two are kinda hash-ish):

With Ruby on Rails 5 you are able to do the following chaining using ActiveRecord::Relation#or method:
```
Person.where(name: 'Neil').or(Person.where(age: 27))
```
Use where_values together with reduce. The unscoped method is necessary only for Rails 4.1+ to ensure default_scope is not included in the where_values. Otherwise predicates from both default_scope and where would be chained with the or operator:
```
Person.where( 
  Person.unscoped.where(name: ['Neil'], age: [27]).where_values.reduce(:or) 
)
```

Install third-party plugins that implement these or similar features, for example:

Where Or (backport of the Ruby on Rails 5 .or feature mentioned above)

Squeel

Person.where{(name == 'Neil') | (age == 27)}

RailsOr
```
Person.where(name: 'Neil').or(age: 27)
```

ActiverecordAnyOf

Person.where.anyof(name: 'Neil', age: 27)

SmartTuple

Person.where(
  (SmartTuple.new(' or ') << {name: 'Neil', age: 27}).compile
)

Use Arel:

Person.where( 
  Person.arel_table[:name].eq('Neil').or(
    Person.arel_table[:age].eq(27)
  ) 
)

Use prepared statements with named parameters:

Person.where('name = :name or age = :age', name: 'Neil', age: 27)

How to execute a raw update sql with dynamic binding in rails

It doesn't look like the Rails API exposes methods to do this generically. You could try accessing the underlying connection and using it's methods, e.g. for MySQL:

st = ActiveRecord::Base.connection.raw_connection.prepare("update table set f1=? where f2=? and f3=?")
st.execute(f1, f2, f3)
st.close

I'm not sure if there are other ramifications to doing this (connections left open, etc). I would trace the Rails code for a normal update to see what it's doing aside from the actual query.

Using prepared queries can save you a small amount of time in the database, but unless you're doing this a million times in a row, you'd probably be better off just building the update with normal Ruby substitution, e.g.

ActiveRecord::Base.connection.execute("update table set f1=#{ActiveRecord::Base.sanitize(f1)}")

or using ActiveRecord like the commenters said.

Enumerating a Postgres result set in Ruby

Perhaps something like this?:

status_counts.
  select { |tuple| %w(processing complete error).include?(tuple['status']) }.
  inject(0) { |memo, tuple| memo += tuple['count'].to_i }

The first step filters the result set to obtain the desired tuples; the second step sums up the counts from the selected tuples. PGResult mixes in Enumerable, so it should work with select/inject.

SQL query (Postgres) works normally, but not through ActiveRecord

You need to double that \ to get \w down to the regex engine and then you have to double each of those to get them past Ruby's string literal handling. And you should use E'' to avoid a warning. Also, you don't need that extra SELECT, you can compare the regexp_matches return value directly. So, something like this should work:

inner join ... and regexp_matches(patent.us_class_current, E'(\\\\w+)/') = array['D02']

There's no need to escape a slash in a PostgreSQL regex so I took that out too. Embedding a language (regex) inside a language (PostgreSQL's SQL) inside another language (Ruby) tends to get a bit messy when they all want to use the same escape character.

For example, in psql these things happen:

psql=> select regexp_matches('D03/pancakes', E'(\w+)/');
 regexp_matches 
----------------
(0 rows)

psql=> select regexp_matches('D03/pancakes', E'(\\w+)/');
 regexp_matches 
----------------
 {D03}
(1 row)

psql=> select regexp_matches('D03/pancakes', E'(\\w+)/') = array['D03'];
 ?column? 
----------
 t
(1 row)

And then from the Rails console:

> ActiveRecord::Base.connection.select_rows(%q{select regexp_matches('D03/pancakes', E'(\w+)/')})
   (0.5ms)  select regexp_matches('D03/pancakes', E'(\w+)/')
 => [] 
> ActiveRecord::Base.connection.select_rows(%q{select regexp_matches('D03/pancakes', E'(\\w+)/')})
   (1.9ms)  select regexp_matches('D03/pancakes', E'(\w+)/')
 => [] 
> ActiveRecord::Base.connection.select_rows(%q{select regexp_matches('D03/pancakes', E'(\\\\w+)/')})
   (0.4ms)  select regexp_matches('D03/pancakes', E'(\\w+)/')
 => [["{D03}"]] 
> ActiveRecord::Base.connection.select_rows(%q{select regexp_matches('D03/pancakes', E'(\\\\w+)/') = array['D03']})
   (1.4ms)  select regexp_matches('D03/pancakes', E'(\\w+)/') = array['D03']
 => [["t"]]

Getting nested objects with Active Record Join without includes

You can add the columns in the select query.

TableA.select('TableA.*, TableB.column1, TableB.column2')
      .where(:b_id => "123")
      .joins("INNER JOIN TableB ON column1='value1' AND column2 = TableB.id")

NOTE: You might not see the columns in the TableA record in rails console. Use as_json to check if you got them in result

How to query WHERE (ColA, ColB, ColC) IN ((a1, b1, c1), (a2, b2, c2), ...)

Now answering my own question as there seems to be no official way with problems like that when using ActiveRecord 4.2.4.

I solved my problem by extending AR with a new method especially made for querying tuples:

module ActiveRecord
  class Base
    class << self

      # not really compatible to the rest of ActiveRecord but it works
      # provide parameters like this: symbols_tuple = [ :col1, :col2, :col3 ]
      # and values_tuples = [ [1, 4, 7], [2, 5, 8], [3, 6, 9]]
      # this will result in a SQL statement like SELECT * FROM table WHERE ((col1, col2, col3) IN ((1,4,7),(2,5,8),(3,6,9)))
      def where_tuple(symbols_tuple, values_tuples)
        tuple_size = symbols_tuple.size

        tuple_part = "(#{(['?']*tuple_size).join(',')})"
        in_stmt = "(#{([tuple_part]*values_tuples.size).join(', ')})"
        stmt = "(#{symbols_tuple.map { |sym| sym.to_s }.join(', ')}) IN #{in_stmt}"

        res = where(stmt, *(values_tuples.flatten!))

        return res
      end

    end # end of class << self
  end # end of class Base
end

This doesn't really integrate too well into the usual ways of ActiveRecord but at least for my cases it does the job.

Please let me know if you can think of a way on how to solve problems like this in a more elegant way!