Class: Invoicing::ConnectionAdapterExt

Inherits:
Object
  • Object
show all
Defined in:
lib/invoicing/connection_adapter_ext.rb

Overview

Extensions specific to certain database adapters. Currently only MySQL and PostgreSQL are supported.

Class Method Summary collapse

Class Method Details

.conditional_function(condition, value_if_true, value_if_false) ⇒ Object

Creates a database-specific SQL fragment for evaluating a three-legged conditional function in a query.



8
9
10
11
12
13
14
15
16
17
 
# File 'lib/invoicing/connection_adapter_ext.rb', line 8

def self.conditional_function(condition, value_if_true, value_if_false)
  case ActiveRecord::Base.connection.adapter_name
    when "MySQL"
      "IF(#{condition}, #{value_if_true}, #{value_if_false})"
    when "PostgreSQL", "SQLite"
      "CASE WHEN #{condition} THEN #{value_if_true} ELSE #{value_if_false} END"
    else
      raise "Database adapter #{ActiveRecord::Base.connection.adapter_name} not supported by invoicing gem"
  end
end

.group_by_all_columns(model_class) ⇒ Object

Suppose A has_many B, and you want to select all As, counting for each A how many Bs it has. In MySQL you can just say:

SELECT A.*, COUNT(B.id) AS number_of_bs FROM A LEFT JOIN B on A.id = B.a_id GROUP BY A.id

PostgreSQL, however, doesn’t like you selecting a column from A if that column is neither in the GROUP BY clause nor wrapped in an aggregation function (even though it is implicitly grouped by through the fact that A.id is unique per row). Therefore for PostgreSQL, we need to explicitly list all of A’s columns in the GROUP BY clause.

This method takes a model class (a subclass of ActiveRecord::Base) and returns a string suitable to be used as the contents of the GROUP BY clause.



30
31
32
33
34
35
36
37
38
39
40
41
42
 
# File 'lib/invoicing/connection_adapter_ext.rb', line 30

def self.group_by_all_columns(model_class)
  case ActiveRecord::Base.connection.adapter_name
    when "MySQL"
      model_class.quoted_table_name + "." +
        ActiveRecord::Base.connection.quote_column_name(model_class.primary_key)
    when "PostgreSQL", "SQLite"
      model_class.column_names.map{ |column|
        model_class.quoted_table_name + "." + ActiveRecord::Base.connection.quote_column_name(column)
      }.join(', ')
    else
      raise "Database adapter #{ActiveRecord::Base.connection.adapter_name} not supported by invoicing gem"
  end
end