Preload tables in ecto dynamically

Hi:
I am working on this library for querying, and I have this problem for preloading tables. My params are like this:

%{
"customer" => %{
"$binding" => :first,
"$include" => %{"users" => %{"$binding" => :last}}
},
"facilities" => %{
"$binding" => :first,
"$include" => %{"instance" => %{"$binding" => :last}}
},
"instances" => %{"$binding" => :first, "$include" => "facilities"}
}

I am successfully able to make joins for all of these thanks to @Eiji help. binding: :first means bind to the main table and binding_ :last bind to the last table. These params are dynamic and contain any number of nested includes.

The problem I am facing is preloading them dynamically. So far I am successful to build dynamic preloads from the above. I wrote a function that loop through the params and build a list like below

               preload: [ {:instances, :facilities}, [customer: :users, facilities: :instance]]>

But as I go one more level deep like this:

%{
"customer" => %{
"$binding" => :first,
"$include" => %{"users" =>  %{"$binding" => :last, "$include" => %{"items" => %{"$binding" => :last}}}}
}

The preload will look like this

 preload: [ {:instances, :facilities}, [customer: {:users, :items}, facilities: :instance]]>

And it’s not very maintainable and I even don’t know how to approach it.

Can anyone please suggest any workaround for this.

Is there any other approach I can take which is not this complex?

Thanks

@script Can you please share your code for collecting preload list and how preload should work for more levels deep? Also some people may not know what you said, so I would just like to your previous question.

Thanks, @Eiji for providing the link to the question. Below is the code. It’s not optimized as your code looks :slightly_smiling_face: but it’s doing the work for now.

def build_preload_query(include) do
Enum.reduce(include, [], fn {k, v}, acc ->
  case v["$binding"] do
    :first ->
      acc ++ preload_last(k, v)

    _ ->
      acc
  end
end)
end

def preload_last(k, v) do
if is_nil(v["$include"]) do
  [string_to_atom(k)]
else
  {include, _map} = Map.pop(v, "$include")

  case include do
    include when is_map(include) ->
      key = Map.keys(include) |> hd()
      [{string_to_atom(k), string_to_atom(key)}]

    _ ->
      []
  end
end
end

This code is looping through the params and if the $include is a map it will use a tuple to preload the nested table with the outer table else just the first table.
The output is this:

    [customer: :users, facilities: :instance]

If I add another $include map inside users as I showed above in my second example. This function should produce preload like this:

 [customer: [users: :items], facilities: :instance]

And if there is another nested include inside items the list looks like this:

[customer: [users: [items: :other_table], facilities: :instance]

Here is my first version:

defmodule Example do
  def sample(map) when is_map(map), do: do_sample(map)

  defp do_sample(map), do: Enum.reduce(map, [], &do_sample/2)

  defp do_sample({key, %{"$include" => include}}, acc) when is_map(include),
    do: [{String.to_atom(key), do_sample(include)} | acc]

  defp do_sample({key, %{"$include" => include}}, acc) when is_bitstring(include),
    do: [{String.to_atom(key), String.to_atom(include)} | acc]

  defp do_sample({key, %{"$binding" => :last}}, _acc), do: String.to_atom(key)

  defp do_sample({_key, _value}, acc), do: acc
end

Please let me know if it works as expected for you in all cases.

1 Like

Thanks @Eiji. I will let you know.

Hi @Eiji. so, I give it a try with below params.

%{
  "$include" => %{
    "facilities" => %{
      "$include" => %{"instance" => %{}}
    },
    "instances" => %{"$include" => %{"facilities" => %{}}},
    "customer" => %{
      "$include" => %{"users" => %{"$include" => %{"facilities" => %{}}}}
    }
  }
} 

The output it produced is this:

facilities
[users: :facilities]
:instance
:facilities
[instances: :facilities, facilities: :instance, customer: [users: :facilities]] 

The last line has correct output but it’s returning all other values before the final result. I think it’s reducing over the params and instead if returning the final output returns all the value it’s reducing over.

@script Sorry, I completely don’t understand.

For me Example.sample(data) returns [] for such input. I have no idea how it become like that. Looks like you did not gave a correct input here.

This is not even 1 list. How 1 call could turn into multiple results? Here looks like you pass few different inputs.

I have no information what happen on your side. Please send me all cases with their expected results. Something like in TDD, so if all tests would pass then implementation is correct.

If you have a public repo simply add tests like:

defmodule ExampleTest do
  use ExUnit.Case

  @first_input […]
  @second_input […]
  @third_input […]

  @first_expected […]
  @second_expected […]
  @third_expected […]

  test "all" do
    assert Example.sample(@first_input) == @first_expected
    assert Example.sample(@second_input) == @second_expected
    assert Example.sample(@third_input) == @third_expected
  end
end

and I will just make a PR for that.

1 Like

Sorry, @Eiji. you are right. it is an issue on my end. I am debugging it. I will let you know.
Thanks for your quick insight.

@Eiji Thanks. It works perfectly as always.

@Eiji sorry Just his one case I forgot to mention if there is a list.

           “instances” => %{"$binding" => :first, “$include” => [“facilities”, “units”]} }

The prelod list looks like this:

                [instances: [:facilities, :units]]

@script Here we go:

defmodule Example do
  def sample(map) when is_map(map), do: do_sample(map)

  defp do_sample(map), do: Enum.reduce(map, [], &do_sample/2)

  defp do_sample({key, %{"$include" => include}}, acc) when is_map(include),
    do: [{String.to_atom(key), do_sample(include)} | acc]

  defp do_sample({key, %{"$include" => include}}, acc) when is_bitstring(include),
    do: [{String.to_atom(key), String.to_atom(include)} | acc]

  defp do_sample({key, %{"$include" => include}}, acc) when is_list(include),
    do: [{String.to_atom(key), Enum.map(include, &String.to_atom/1)} | acc]

  defp do_sample({key, %{"$binding" => :last}}, _acc), do: String.to_atom(key)

  defp do_sample({_key, _value}, acc), do: acc
end

Just one extra function clausule. It’s why I love writing configurable and easy to maintain code! :077:

1 Like

@Eiji sorry for bothering you again and again. I found a case where it’s not returning correct output.

       %{
       "antibiotic" => %{"$binding" => :first},
       "prescribing_clinician" => %{"$binding" => :first}
       }

the above code returning empty preload.

If I add this:

      defp do_preloading({key, %{"$binding" => :first}}, _acc), do: String.to_atom(key)

It’s returning only one preload:

    preload: [:prescribing_clinician]

@script I’m a bit lost or don’t remember something. Should not it be:

%{
  "antibiotic" => %{"$binding" => :last},
  "prescribing_clinician" => %{"$binding" => :last}
}

If I remember it correct $binding set to :last should be a leaf and should not have $include inside it. If so then your input is invalid, right?

Also I made one small mistake with return (noticed when you gave me this line after changes):

defmodule Example do
  def sample(map) when is_map(map), do: do_sample(map)

  defp do_sample(map), do: Enum.reduce(map, [], &do_sample/2)

  defp do_sample({key, %{"$include" => include}}, acc) when is_map(include),
    do: [{String.to_atom(key), do_sample(include)} | acc]

  defp do_sample({key, %{"$include" => include}}, acc) when is_bitstring(include),
    do: [{String.to_atom(key), String.to_atom(include)} | acc]

  defp do_sample({key, %{"$include" => include}}, acc) when is_list(include),
    do: [{String.to_atom(key), Enum.map(include, &String.to_atom/1)} | acc]

  # before I did not used `acc` here:
  defp do_sample({key, %{"$binding" => :last}}, acc), do: [String.to_atom(key) | acc]

  defp do_sample({_key, _value}, acc), do: acc
end

If we would call it with such input:

data = %{
  "antibiotic" => %{"$binding" => :last},
  "prescribing_clinician" => %{"$binding" => :last}
}

data |> Example.sample() |> IO.inspect()

then it would return:

[:prescribing_clinician, :antibiotic]

Again the better way is to create a test for all cases. Once you do it you should have all information you need.

Also looks like that you do not understand my code, so I would describe it:

defmodule Example do
  # Enum.reduce/3 would be used multiple times
  # this is function to work with only top level
  # so reduce which happens also nested is called in separate function
  # in order to allow easy change of code
  def sample(map) when is_map(map), do: do_sample(map)

  # this is used in `sample/1` and next clausule
  defp do_sample(map), do: Enum.reduce(map, [], &do_sample/2)

  # checks if our value have map with `$include` as key
  # then we also check if `include` is map which is important for rest clausules
  defp do_sample({key, %{"$include" => include}}, acc) when is_map(include),
    do: [{String.to_atom(key), do_sample(include)} | acc]

  # checks if our value have map with `$include` as key
  # then we also check if `include` is string which is important for rest clausules
  defp do_sample({key, %{"$include" => include}}, acc) when is_bitstring(include),
    do: [{String.to_atom(key), String.to_atom(include)} | acc]

  # checks if our value have map with `$include` as key
  # then we also check if `include` is list
  # as you did not gave a example with list of maps
  # I made it this clausule working with strings only
  defp do_sample({key, %{"$include" => include}}, acc) when is_list(include),
    do: [{String.to_atom(key), Enum.map(include, &String.to_atom/1)} | acc]

  # as described before if we have leaf then we do not go nested
  # this is only a small optimization in order to call another iterate again
  defp do_sample({key, %{"$binding" => :last}}, acc), do: [String.to_atom(key) | acc]

  # if nothing else matches then simply return acc
  # you can debug here which things does not match
  defp do_sample({_key, _value}, acc), do: acc
end

Also don’t worry in case of any questions. :smiley:

That explains it perfectly. Thank you again @Eiji for your time and explanation.

P.S: Any coding tips to write clean code as you do.:grinning:

In short? Of course: PRACTICE :077:

This topic may be interesting for you:

Personally this quote is probably one of my best (for this specific question):

1 Like