用布尔值为rails中的SQL视图创建迁移

Question

我正在使用SQLite视图，如下所示：

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

正如您可以看到的，valido='f'是硬编码的，但是我将来需要使用MySQL，我使用execute方法运行这个查询，如何为每个适配器"mysql、sqlite、postgresql等等“创建正确的查询。用于创建SQL视图。

Answer 1

我找到了解决办法：

# 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

我有自己的解决方案来处理SQL视图：https://github.com/igorkasyanchuk/sql_view

您可以使用它将AR代码转换为SQL，并在以后与AR模型一样使用视图。