3
\$\begingroup\$

I have a few raw queries which are similar to each other in that it is mostly duplicate code with the difference being that one returns structs and the other returns counts of how many there are.

How would it be best to reduce this into possibly one function? I was thinking that maybe a opts \\ :return_struct would work for checking to see if a count was requested or the objects themselves.

  @doc """
  Returns the lazy for a time period of days. Lazy customers 
  are the ones that have not had a transaction in a period of time 
  """
  def lazy_customers(merchant_id, date) do
    sql = """
      WITH lazy_transactions AS (
        WITH new_transactions AS (
             select * from transactions
             where merchant_id = $1 and inserted_at > $2::date
           ), older_transactions AS (
                select * from transactions
                where merchant_id = $1 and inserted_at < $2::date
             )
        SELECT * from older_transactions
        WHERE user_id NOT IN (select user_id from new_transactions)
      )
      SELECT * from users where id IN (select user_id from lazy_transactions)
    """

    result = Ecto.Adapters.SQL.query!(MyApp.Repo, sql, [merchant_id, string_to_date(Date.to_string(date))])
    Enum.map(result.rows, &MyApp.Repo.load(User, {result.columns, &1}))   
  end

  @doc """
  Returns the lazy for a time period of days. Lazy customers 
  are the ones that have not had a transaction in a period of time 
  """
  def lazy_customers_count(merchant_id, date) do
    sql = """
      WITH lazy_transactions AS (
        WITH new_transactions AS (
             select * from transactions
             where merchant_id = $1 and inserted_at > $2::date
           ), older_transactions AS (
                select * from transactions
                where merchant_id = $1 and inserted_at < $2::date
             )
        SELECT * from older_transactions
        WHERE user_id NOT IN (select user_id from new_transactions)
      )
      SELECT COUNT(id) FROM users WHERE id IN (SELECT user_id FROM lazy_transactions)
    """

    result = Ecto.Adapters.SQL.query!(MyApp.Repo, sql, [merchant_id, string_to_date(Date.to_string(date))])
    result.rows
      |> List.flatten
      |> List.first
  end

The real differences is on the bottom line of the raw SQL where either SELECT * FROM ... or SELECT COUNT(id) FROM ... is changed. The next change is how result is handling in terms of returning a count or an array of %Transaction{} structs.

How can I DRY this up a little?

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.