How do I access a Foreign Key value in this code and sort on it?

I have a Phoenix Liveview app that iterates through an Ecto collection named Testbed. The properties of Testbed are rendered to the Liveview via Heex and users can look at them . Some of these have event handlers that do stuff when people click on them.

I have another Ecto collection name Group. Group has a foreign key relationship with Testbed.

In my heex code I can access Group just like any other property on Testbed.



  <th scope="col" class="px-6 rounded-l-full "  phx-click="sort_by_string" phx-value-field="group">Group</th>

When a user clicks that DOM element it runs the sorting code below and phx-value-field=“group” is passed to the field property. The Testbeds are then sorted by Group.

If I passed name to the field property, the Testbeds would be sorted by their name.


  def sort_by(socket, field) do
    atomParam = String.to_existing_atom(field)

    if socket.assigns[:sort_direction] == :desc do
      sorted_testbeds = Enum.sort_by(socket.assigns.testbeds, fn item -> Map.get(item, atomParam) end)
      {:noreply, assign(socket, testbeds: sorted_testbeds, sort_direction: :asc, sort_field: atomParam)}
    else
      sorted_testbeds = Enum.sort_by(socket.assigns.testbeds, fn item -> Map.get(item, atomParam) end)
      |> Enum.reverse()
      {:noreply, assign(socket, testbeds: sorted_testbeds, sort_direction: :desc, sort_field: atomParam)}
    end
  end


  def handle_event("sort_by_string", %{"field" => field}, socket) do
    sort_by(socket, field)
  end

My problem is I explicitly want the Testbeds sorted by group.name

I am unsure how to access group.name in my current code or how to explicitly access any specific property on the Group. If I do: phx-value-field=“group.name” I am unsure if this is valid syntax to pass and if it is, how do I handle it and convert it to an atom so I can use it in my code?

My current code sorts the group but it does it in an oddball way where groups aren’t completely sorted alphabetically by name.

There are two paths you can take here. One would be to denormalize the “group name” field into your “testbed”.

In your schema, you would add a virtual field group_name:

defmodule Testbed do
  schema do
    # ...
    field :group_name, :string, virtual: true
  end
end

And then in your query you can join that in manually:

Repo.all(
  from tb in Testbed,
    where: [...],
    join: g in assoc(tb, :group),
    select: %Testbed{tb | group_name: g.name}
)

Alternatively, you could instead modify your sort event to accept a second field:

def handle_event("sort", %{"field" => field} = params, socket) do
  field = String.to_existing_atom(field)
  fields = case Map.get(params, "subfield") do
    nil -> [field]
    subfield -> [field, String.to_existing_atom(subfield)]
  end
  # ...
  Enum.sort_by(testbeds, fn tb -> get_in(tb, fields) end)
end
1 Like

Thanks. I’m going to have to play with this for awhile as I am unfamiliar with the thought process behind any of this. The Join process is something I should probably be familiar with.

I’m wondering if it would be simpler to iterate through the Groups collection directly and sort them alphabetically (downcase them and compensate for non alphanumeric characters) by Group.name .

Then, extract the order of all Group.id’s from this sorted list.

Then, apply that same order to all Testbeds via Testbed.group_id

Instead of passing the field as a string to your sort_by/2 function, you could pass a mapper function, and then combine that with a special case function clause for group:

  def handle_event("sort_by_string", %{"field" => "group"}, socket) do
    sort_by(socket, &(&1.group.name))
  end

  def handle_event("sort_by_string", %{"field" => field}, socket) do
    field = String.to_existing_atom(field)
    sort_by(socket, &Map.get(&1, field))
  end

Just to be clear, there are two different solutions in my comment.

For the first solution, I showed you how to join only a single column from Group onto a Testbed via a virtual field.

For the second solution, I assumed you were already joining the entire Group onto each Testbed (which is common). The way you would perform that type of join (efficiently) is to use a query with a preload, like this:

Repo.all(
  from tb in Testbed,
    where: [...],
    join: g in assoc(tb, :group),
    preload: [group: g]
)
# Returns something like:
[%Testbed{name: "whatever", group: %Group{...}}, ...]

Then you could update your sorting code to use get_in/2 like I demonstrated.

See the docs for Ecto.Query.preload/3.

You are welcome to try this of course (it is your own work), but I do not think it would be simpler, no. You open yourself up to a lot of problems with regards to keeping the groups and testbeds in sync with each other, plus the extra complexity.