export query for ecto

build_export_query.ex

def build_export_query(user, batch_size \\ 500)
  columns = ~w(id name amount amount_currency cycle first_bill_date type type_description)

  query = """
    COPY (
      SELECT #{Enum.join(columns, ",")}
      FROM subscriptions
      WHERE archived = false
      AND user_id = #{user.id}
    ) to STDOUT WITH CSV DELIMITER ',';
  """

  csv_header = [Enum.join(columns, ","), "\n"]

  Ecto.Adapters.SQL.stream(Repo, query, [], max_rows: batch_size)
  |> Stream.map(&(&1.rows))
  |> (fn stream -> Stream.concat(csv_header, stream) end).()
end