Use dynamics as an acc in reduce

Note: I’m assuming that that first code block is surrounded in some kind of Enum.reduce structure. If it isn’t, please post the rest of the code.

Probably want to pass the result of the previous iteration here instead of true.

I’m not entirely sure I understand all the nuances of your particular use case - but this seems to work:

defmodule Playground do
  import Ecto.Query
  alias MusicDB.Repo
  alias MusicDB.{Album, Track}

  @track_field_map Map.new([:id, :inserted_at], &{to_string(&1), &1})

  defp track_join(base, additional),
    do: Enum.reduce(additional, base, &append_track_field_join/2)

  defp append_track_field_join({field_name, conditions}, acc_join) do
    case Map.fetch(@track_field_map, field_name) do
      {:ok, name} ->
        Enum.reduce(
          conditions,
          acc_join,
          fn {operator, params}, join ->
            append_track_join_condition(operator, params, name, join)
          end
        )

      _ ->
        # unknown field
        acc_join
    end
  end

  defp append_track_join_condition("$in", list, name, join) do
    dynamic([track: t], ^join and field(t, ^name) in ^list)
  end

  defp append_track_join_condition("$between", {low, high} = bounds, name, join) do
    {min, max} = if(low < high, do: bounds, else: {high, low})
    dynamic([track: t], ^join and field(t, ^name) >= ^min and field(t, ^name) <= ^max)
  end

  defp append_track_join_condition(_unknown_operator, _params, _name, join) do
    join
  end

  def query do
    base_join = dynamic([album: a, track: t], a.id == t.album_id)

    additional_join = %{
      "id" => %{"$in" => [2, 3, 4]},
      "inserted_at" => %{"$between" => {~N[2019-05-04 00:00:00], ~N[2019-05-03 00:00:00]}}
    }

    join_on = track_join(base_join, additional_join)

    query =
      from(a in Album,
        as: :album,
        join: t in Track,
        as: :track,
        on: ^join_on,
        where: a.id == 1,
        select: {t.id, t.inserted_at}
      )
  end

  def play do
    query()
    |> Repo.all()
    |> IO.inspect()

    :ok
  end
end

Thanks for your answer.

sorry if it’s too vague to understand. Let me explain more:

Background:

I am writing this join query that takes multiple options for on in joins and build a query dynamically. The fields can change so I am trying to implement a dynamic solution. I need some method that iterates over these params:

      "$additional_on_clauses" => %{
        "infection_category_id" => %{"$in" => category_ids},
        "event_date" => %{"$between_equal" => [start_date, end_date]}
      }

And build a dynamic query expression that I interpolate later in my join clause like this:

  from query. join: on: ^dynamics.

The problem is my current implementation make dynamic query for just one map inside the "$additional_on_clauses" . And ignore the other one.

The params inside "$additional_on_clauses" can vary. And field names also change. The only thing constant will be $in, $between_equal. Based on these two I will build queries dynamically.

Hope this helps.

And ignore the other one.

Not sure what you mean by ignoring something - looking at

"$additional_on_clauses" => %{
  "infection_category_id" => %{"$in" => category_ids},
  "event_date" => %{"$between_equal" => [start_date, end_date]}
}

I thought you want something like

t1."infection_category_id" = ANY($1) AND t1."event_date" >= $2 AND t1."event_date" <= $3 [[2, 3, 4], ~N[2019-05-03 00:00:00], ~N[2019-05-04 00:00:00]]

provided

category_ids = [2,3,4]
start_date = ~N[2019-05-03 00:00:00]
end_date = ~N[2019-05-04 00:00:00]

The code I showed will act in exactly that manner.

This is what is unclear - what do you mean by both the above maps inside additional_on_clauses?

The code you supplied can only process

%{
  "event_date" => %{"$between_equal" => [start_date, end_date]}
}

or

%{
  "infection_category_id" => %{"$in" => category_ids},
  "event_date" => %{"$between_equal" => [start_date, end_date]}
}

So when you say both the above maps it sounds like you are referring to

%{"$in" => category_ids}

and

%{"$between_equal" => [start_date, end_date]}

in

%{
  "infection_category_id" => %{"$in" => category_ids},
  "event_date" => %{"$between_equal" => [start_date, end_date]}
}

Now I’m wondering what

"$additional_on_clauses" => %{
  "event_date" => %{"$between_equal" => [start_date, end_date]}
}

"$additional_on_clauses" => %{
  "infection_category_id" => %{"$in" => category_ids},
  "event_date" => %{"$between_equal" => [start_date, end_date]}
}

actually means because

  def play do
    %{
      "$additional_on_clauses" => %{
        "event_date" => %{"$between_equal" => [~N[2019-05-01 00:00:00], ~N[2019-05-02 00:00:00]]}
      },
      "$additional_on_clauses" => %{
        "infection_category_id" => %{"$in" => [2, 3, 4]},
        "event_date" => %{"$between_equal" => [~N[2019-05-03 00:00:00], ~N[2019-05-04 00:00:00]]}
      }
    }
  end

evaluates to

%{
  "$additional_on_clauses" => %{
    "event_date" => %{
      "$between_equal" => [~N[2019-05-03 00:00:00], ~N[2019-05-04 00:00:00]]
    },
    "infection_category_id" => %{"$in" => [2, 3, 4]}
  }
}

because the last "$additional_on_clauses" value will replace the first "$additional_on_clauses" value.

Thanks for your time.

I am talking about Ecto Dynamics. https://hexdocs.pm/ecto/Ecto.Query.html#dynamic/2

I am using them inside Enum.reduce statement. So when I pass this map:

%{
"$additional_on_clauses" => %{
"event_date" => %{
  "$between_equal" => [~N[2019-05-03 00:00:00], ~N[2019-05-04 00:00:00]]
},
"infection_category_id" => %{"$in" => [2, 3, 4]}

}
}

First it takes this first map and make a dynamic query like this:

  dynamic([q, c],
                   q.event_date >= ^~N[2000-01-01 00:00:00] and q.event_date <= ^~N[2019-06-20 00:00:00])

And then Enum.reduce takes the second map in the next iteration and build query like this:

 dynamic([q, c], q.infection_category_id in ^[9542]). 

But I want these two to be combined like this inside Enum.reduce.

     dynamic([q, c],
                   q.event_date >= ^~N[2000-01-01 00:00:00] and q.event_date <= ^~N[2019-06-20 00:00:00] and
                   q.infection_category_id in ^[9542]) 

so far I have this:

Enum.reduce(additional_on_clauses, true, fn {field, map}, _query ->
  query =
    if Map.has_key?(map, "$in") do
      query =
        dynamic(
          [q],
          field(
            q,
            ^String_to_atom(field)
          ) in ^map["$in"] and ^query
        )
    else
      query
    end


  if Map.has_key?(map, "$between_equal") do
    query =
      dynamic(
        [q],
        field(q, ^String_to_existing_atom(field)) >= ^Enum.min(map["$between_equal"]) and
          field(q, ^String_to_existing_atom(field)) <= ^Enum.max(map["$between_equal"]) and
          ^query
      )
  else
    query
  end
end)

But It only returns query for the last map which in this case is this one:

    "infection_category_id" => %{"$in" => [2, 3, 4]}

I understand the behaviour thats how Enum.reduce works. But Is there a way I can combine these two together.

The fields can be dynamic so may be next time the fields will b end_date instead of event_date and id instead of infection_category_id. So i can’t hard code these.

So is there a way beside Enum.reduce or with Enum.reduce that I can get the desired result.

Thanks

This code strongly suggests that the problem is somewhere else in your code (something that you aren’t showing or even looking at):

defmodule Demo do
  def run(additional_on_clauses) do
    Enum.reduce(additional_on_clauses, true, fn {field, map}, query ->
      query =
        if Map.has_key?(map, "$in") do
          # dynamic([q], field(q, ^String_to_atom(field)) in ^map["$in"] and ^query)
          "#{field} in #{inspect(map["$in"])} and #{query}"
        else
          query
        end

      if Map.has_key?(map, "$between_equal") do
        # dynamic([q],
        #  field(q, ^String_to_existing_atom(field)) >= ^Enum.min(map["$between_equal"]) and
        #  field(q, ^String_to_existing_atom(field)) <= ^Enum.max(map["$between_equal"]) and
        #  ^query
        # )
        "#{field} >= #{Enum.min(map["$between_equal"])} and " <>
          "#{field} <= #{Enum.max(map["$between_equal"])} and " <>
          "#{query}"
      else
        query
      end
    end)
  end
end

data = %{
  "$additional_on_clauses" => %{
    "event_date" => %{"$between_equal" => [~N[2019-05-03 00:00:00], ~N[2019-05-04 00:00:00]]},
    "infection_category_id" => %{"$in" => [2, 3, 4]}
  }
}

data["$additional_on_clauses"]
|> Demo.run()
|> IO.inspect()

$ elixir demo.exs
"infection_category_id in [2, 3, 4] and event_date >= 2019-05-03 00:00:00 and event_date <= 2019-05-04 00:00:00 and true"

And to my earlier point changing to this:

data = %{
  "$additional_on_clauses" => %{
    "event_date" => %{"$between_equal" => [~N[2019-05-03 00:00:00], ~N[2019-05-04 00:00:00]]},
    "infection_category_id" => %{"$in" => [2, 3, 4]}
  },
  "$additional_on_clauses" => %{
    "infection_category_id" => %{"$in" => [2, 3, 4]}
  }
}

data["$additional_on_clauses"]
|> Demo.run()
|> IO.inspect()

has the following result:

$ elixir demo.exs
warning: key "$additional_on_clauses" will be overridden in map
  demo.exs:28

"infection_category_id in [2, 3, 4] and true"

Thanks for your suggestion I updated the code and it seems to be working so the issue is was on my side.