Creating migrations for SQL views in rails with boolean values

Question

Iam using SQLite wit a view like this one:

CREATE VIEW view_importaciones AS
      SELECT fecha_importacion, COUNT(DISTINCT(total)) - 1 AS total, COUNT(DISTINCT(errores)) -1 AS errores, estado FROM
        (
          SELECT fecha_importacion, id AS total, 0 as errores, estado FROM marcas WHERE parent_id = 0
          UNION
          SELECT fecha_importacion, 0 AS total, id as errores, estado FROM marcas WHERE valido = 'f' AND parent_id = 0
        ) AS importaciones GROUP BY fecha_importacion ORDER BY fecha_importacion

As you can see valido='f' is hard coded but I will need to use MySQL in the future, I run this query using the execute method how can I create the correct query for each adapter "mysql, sqlite, postgresql,etc.." for creating the SQL view.

Answer 1

I found the solution:

# First create the sql needed with the parameters, this way
# the query will change depeding on the database
sql = Marca.send(:construct_finder_sql, 
                  :select => "fecha_importacion, 0 AS total, id AS errores, estado",
                  :conditions => { :valido => false, :parent_id => 0}
                  :group => "marcas.fecha_importacion"
                 )

# Add the sql where needed
sql = "CREATE VIEW view_importaciones AS
  SELECT fecha_importacion, COUNT(DISTINCT(total)) - 1 AS total, COUNT(DISTINCT(errores)) -1 AS errores, estado FROM
    (
      SELECT fecha_importacion, id AS total, 0 as errores, estado FROM marcas WHERE parent_id = 0
      UNION
      #{sql}
    ) AS importaciones GROUP BY fecha_importacion ORDER BY fecha_importacion"
# Run the sql
execute(sql)

Answer 2

I've own solution to work SQL views: https://github.com/igorkasyanchuk/sql_view

You can use it to convert AR code to SQL and later work with view as with AR model.