Skip to content

Instantly share code, notes, and snippets.

@noelworden

noelworden/SQL-to-Ecto.md

Last active August 8, 2020 22:46
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save noelworden/0f3c7fa96b2cc4ab5a909b2935891b89 to your computer and use it in GitHub Desktop.
Save noelworden/0f3c7fa96b2cc4ab5a909b2935891b89 to your computer and use it in GitHub Desktop.
Download ZIP
#helpers
Raw
SQL-to-Ecto.md

Overview of all queries

SQL

WITH latest_imports AS (
      SELECT
        i.*
      FROM (
        SELECT
          *,
          row_number() OVER (
            PARTITION BY
              period,
              vendor_id,
              business_unit_id,
              processor_id,
              data_type_id,
              descriptor_id
            ORDER BY
              inserted_at DESC
          ) AS row_number
        FROM
          raw_imports
      ) AS i
      WHERE
        i.row_number = 1
    ),

 import_products AS (
  SELECT DISTINCT import_id, metric as product_name FROM raw_cayan_product_detail AS r
  UNION ALL
  SELECT DISTINCT import_id, fees as product_name FROM raw_nmi_product_detail AS r
  UNION ALL
  SELECT DISTINCT import_id, product_description as product_name FROM raw_vantiv_product_detail AS r
),

unmapped_import_products AS (
	SELECT import_id, product_name FROM import_products AS ip
	INNER JOIN raw_imports as ri
	ON ip.import_id = ri.id
	LEFT JOIN products AS p
	ON p.vendor_id = ri.vendor_id 
	AND
	p.name = ip.product_name
	WHERE p.id is null
)

-- For the 'show' page
-- Select * from unmapped_import_products WHERE import_id = 1

-- For the Index page
-- SELECT id, period, business_unit_id, descriptor_id, vendor_id, data_type_id, COUNT(import_id) AS unmapped_count FROM latest_imports AS li
-- LEFT JOIN unmapped_import_products AS uip
-- ON uip.import_id = li.id
-- GROUP BY id, period, inserted_at, updated_at, vendor_id, business_unit_id, processor_id, descriptor_id, data_type_id, import_id

Ecto

def get_latest do
    ordered_imports =
      from i in Import,
        select: %{
          id: i.id,
          period: i.period,
          vendor_id: i.vendor_id,
          business_unit_id: i.business_unit_id,
          processor_id: i.processor_id,
          data_type_id: i.data_type_id,
          descriptor_id: i.descriptor_id,
          inserted_at: i.inserted_at,
          updated_at: i.updated_at,
          row_number: over(row_number(), :ordered)
        },
        windows: [
          ordered: [
            partition_by: [
              i.period,
              i.vendor_id,
              i.business_unit_id,
              i.processor_id,
              i.data_type_id,
              i.descriptor_id
            ],
            order_by: [desc: i.inserted_at]
          ]
        ]

    latest_imports =
      from i in subquery(ordered_imports),
        where: i.row_number == 1

    full_structs =
      from(i in subquery(latest_imports),
        select: %Import{
          id: i.id,
          period: i.period,
          vendor_id: i.vendor_id,
          business_unit_id: i.business_unit_id,
          processor_id: i.processor_id,
          data_type_id: i.data_type_id,
          descriptor_id: i.descriptor_id,
          inserted_at: i.inserted_at,
          updated_at: i.updated_at
        }
      )

    from(i in subquery(full_structs),
      join: v in assoc(i, :vendor),
      order_by: [desc: i.period, asc: v.name]
    )
  end

  def import_products do
    cayan = from cayan in Finance.Raw.Schemas.CayanProductDetail,
      distinct: true,
      select: %{product_name: cayan.metric, import_id: cayan.import_id}
    cayan_nmi = from nmi in Finance.Raw.Schemas.NmiProductDetail,
      distinct: true,
      select: %{product_name: nmi.fees, import_id: nmi.import_id},
      union_all: ^cayan
    from vantiv in Finance.Raw.Schemas.VantivProductDetail,
      distinct: true,
      select: %{product_name: vantiv.product_description,
      import_id: vantiv.import_id},
      union_all: ^cayan_nmi
  end

  def unmapped_import_products do
    from(ip in subquery(import_products),
    join: i in Finance.Raw.Schemas.Import,
    on: i.id == ip.import_id,
    left_join: p in Finance.Schemas.Product,
    on: p.vendor_id == i.vendor_id and p.name == ip.product_name,
    where: is_nil(p.id))
  end

  def get_unmapped_products(id) do
    from(u in subquery(unmapped_import_products),
    where: u.import_id == ^id)

    |> Repo.all
  end

  def latest_imports_with_unmapped_products do
    from(li in subquery(get_latest),
    left_join: uip in subquery(unmapped_import_products),
    on: uip.import_id == li.id,
    select: %Import{
      id: li.id,
      period: li.period,
      vendor_id: li.vendor_id,
      business_unit_id: li.business_unit_id,
      processor_id: li.processor_id,
      data_type_id: li.data_type_id,
      descriptor_id: li.descriptor_id,
      unmapped_count: count(uip.import_id),
      inserted_at: li.inserted_at,
      updated_at: li.updated_at
    },
    group_by: [
      li.id,
      li.period,
      li.vendor_id,
      li.business_unit_id,
      li.processor_id,
      li.descriptor_id,
      li.data_type_id,
      li.inserted_at,
      li.updated_at,
      uip.import_id]
    )

    |> Repo.all()
    |> Repo.preload([:business_unit, :vendor, :processor, :data_type, :descriptor])
  end

Individual breakdowns

Latest Imports

WITH latest_imports AS (
      SELECT
        i.*
      FROM (
        SELECT
          *,
          row_number() OVER (
            PARTITION BY
              period,
              vendor_id,
              business_unit_id,
              processor_id,
              data_type_id,
              descriptor_id
            ORDER BY
              inserted_at DESC
          ) AS row_number
        FROM
          raw_imports
      ) AS i
      WHERE
        i.row_number = 1
    ),

def get_latest do
    ordered_imports =
      from i in Import,
        select: %{
          id: i.id,
          period: i.period,
          vendor_id: i.vendor_id,
          business_unit_id: i.business_unit_id,
          processor_id: i.processor_id,
          data_type_id: i.data_type_id,
          descriptor_id: i.descriptor_id,
          inserted_at: i.inserted_at,
          updated_at: i.updated_at,
          row_number: over(row_number(), :ordered)
        },
        windows: [
          ordered: [
            partition_by: [
              i.period,
              i.vendor_id,
              i.business_unit_id,
              i.processor_id,
              i.data_type_id,
              i.descriptor_id
            ],
            order_by: [desc: i.inserted_at]
          ]
        ]

    latest_imports =
      from i in subquery(ordered_imports),
        where: i.row_number == 1

    full_structs =
      from(i in subquery(latest_imports),
        select: %Import{
          id: i.id,
          period: i.period,
          vendor_id: i.vendor_id,
          business_unit_id: i.business_unit_id,
          processor_id: i.processor_id,
          data_type_id: i.data_type_id,
          descriptor_id: i.descriptor_id,
          inserted_at: i.inserted_at,
          updated_at: i.updated_at
        }
      )

    from(i in subquery(full_structs),
      join: v in assoc(i, :vendor),
      order_by: [desc: i.period, asc: v.name]
    )
  end

Import Products

import_products AS (
  SELECT DISTINCT import_id, metric as product_name FROM raw_cayan_product_detail AS r
  UNION ALL
  SELECT DISTINCT import_id, fees as product_name FROM raw_nmi_product_detail AS r
  UNION ALL
  SELECT DISTINCT import_id, product_description as product_name FROM raw_vantiv_product_detail AS r
),

def import_products do
    cayan = from cayan in Finance.Raw.Schemas.CayanProductDetail,
      distinct: true,
      select: %{product_name: cayan.metric, import_id: cayan.import_id}
    cayan_nmi = from nmi in Finance.Raw.Schemas.NmiProductDetail,
      distinct: true,
      select: %{product_name: nmi.fees, import_id: nmi.import_id},
      union_all: ^cayan
    from vantiv in Finance.Raw.Schemas.VantivProductDetail,
      distinct: true,
      select: %{product_name: vantiv.product_description,
      import_id: vantiv.import_id},
      union_all: ^cayan_nmi
  end

Unmapped Import Products

unmapped_import_products AS (
	SELECT import_id, product_name FROM import_products AS ip
	INNER JOIN raw_imports as ri
	ON ip.import_id = ri.id
	LEFT JOIN products AS p
	ON p.vendor_id = ri.vendor_id 
	AND
	p.name = ip.product_name
	WHERE p.id is null
)

def unmapped_import_products do
    from(ip in subquery(import_products),
    join: i in Finance.Raw.Schemas.Import,
    on: i.id == ip.import_id,
    left_join: p in Finance.Schemas.Product,
    on: p.vendor_id == i.vendor_id and p.name == ip.product_name,
    where: is_nil(p.id))
  end

Select single record (show page)

Select * from unmapped_import_products WHERE import_id = 1

def get_unmapped_products(id) do
    from(u in subquery(unmapped_import_products),
    where: u.import_id == ^id)

    |> Repo.all
  end

Select all records (index page)

-- SELECT id, period, business_unit_id, descriptor_id, vendor_id, data_type_id, COUNT(import_id) AS unmapped_count FROM latest_imports AS li
-- LEFT JOIN unmapped_import_products AS uip
-- ON uip.import_id = li.id
-- GROUP BY id, period, inserted_at, updated_at, vendor_id, business_unit_id, processor_id, descriptor_id, data_type_id, import_id

def latest_imports_with_unmapped_products do
    from(li in subquery(get_latest),
    left_join: uip in subquery(unmapped_import_products),
    on: uip.import_id == li.id,
    select: %Import{
      id: li.id,
      period: li.period,
      vendor_id: li.vendor_id,
      business_unit_id: li.business_unit_id,
      processor_id: li.processor_id,
      data_type_id: li.data_type_id,
      descriptor_id: li.descriptor_id,
      unmapped_count: count(uip.import_id),
      inserted_at: li.inserted_at,
      updated_at: li.updated_at
    },
    group_by: [
      li.id,
      li.period,
      li.vendor_id,
      li.business_unit_id,
      li.processor_id,
      li.descriptor_id,
      li.data_type_id,
      li.inserted_at,
      li.updated_at,
      uip.import_id]
    )

    |> Repo.all()
    |> Repo.preload([:business_unit, :vendor, :processor, :data_type, :descriptor])
  end

General Notes

  • The CTE (Common Table Expression) is equivelant to a function in Ecto
  • The Repo.all call should be made in the last function
  • If fields need to be preloaded, the query results will need to be in the form of a struct
    • In this example, the Import struct was used
    • A virtual field for unmapped_count was added to the Import schema to accept the new field brought in by the subquerying
    • field :unmapped_count, :integer, virtual: true

Struct Examples

Basic Struct

[
  %{
    business_unit_id: 4,
    data_type_id: 3,
    descriptor_id: nil,
    id: 1,
    inserted_at: ~U[2020-01-21 22:52:44Z],
    period: ~D[2019-09-01],
    processor_id: 20,
    unmapped_count: 1,
    updated_at: ~U[2020-01-21 22:52:44Z],
    vendor_id: 21
  }
]

Struct with Schema --without preloads

[
  %Finance.Raw.Schemas.Import{
    __meta__: #Ecto.Schema.Metadata<:loaded, "raw_imports">,
    business_unit: #Ecto.Association.NotLoaded<association :business_unit is not loaded>,
    business_unit_id: 4,
    data_type: #Ecto.Association.NotLoaded<association :data_type is not loaded>,
    data_type_id: 3,
    descriptor: #Ecto.Association.NotLoaded<association :descriptor is not loaded>,
    descriptor_id: nil,
    id: 1,
    inserted_at: ~U[2020-01-21 22:52:44Z],
    period: ~D[2019-09-01],
    processor: #Ecto.Association.NotLoaded<association :processor is not loaded>,
    processor_id: 20,
    unmapped_count: 1,
    updated_at: ~U[2020-01-21 22:52:44Z],
    vendor: #Ecto.Association.NotLoaded<association :vendor is not loaded>,
    vendor_id: 21
  }
]

Struct with Schema --with preloads

 %Finance.Raw.Schemas.Import{
    __meta__: #Ecto.Schema.Metadata<:loaded, "raw_imports">,
    business_unit: %Finance.Schemas.BusinessUnit{
      __meta__: #Ecto.Schema.Metadata<:loaded, "business_units">,
      company: #Ecto.Association.NotLoaded<association :company is not loaded>,
      company_id: 2,
      id: 4,
      inserted_at: ~U[2020-01-21 22:50:59Z],
      key: "payline",
      name: "Payline",
      updated_at: ~U[2020-01-21 22:50:59Z]
    },
    business_unit_id: 4,
    data_type: %Finance.Schemas.DataType{
      __meta__: #Ecto.Schema.Metadata<:loaded, "data_types">,
      id: 3,
      inserted_at: ~U[2020-01-21 22:50:58Z],
      key: "product_detail",
      name: "Product Detail",
      updated_at: ~U[2020-01-21 22:50:58Z]
    },
    data_type_id: 3,
    descriptor: nil,
    descriptor_id: nil,
    id: 1,
    inserted_at: ~U[2020-01-21 22:52:44Z],
    period: ~D[2019-09-01],
    processor: %Finance.Schemas.Processor{
      __meta__: #Ecto.Schema.Metadata<:loaded, "processors">,
      id: 20,
      inserted_at: ~U[2020-01-21 22:51:06Z],
      key: "vantiv",
      name: "Vantiv",
      updated_at: ~U[2020-01-21 22:51:06Z]
    },
    processor_id: 20,
    unmapped_count: 1,
    updated_at: ~U[2020-01-21 22:52:44Z],
    vendor: %Finance.Schemas.Vendor{
      __meta__: #Ecto.Schema.Metadata<:loaded, "vendors">,
      id: 21,
      inserted_at: ~U[2020-01-21 22:50:58Z],
      key: "vantiv",
      name: "Vantiv",
      updated_at: ~U[2020-01-21 22:50:58Z]
    },
    vendor_id: 21
  }
]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment