How to order_by an external column in a many-to-many relationship

Questions / Help
1

Hello,

I have two resources and a many_to_many association between them - :websites and :tag. Websites has a integer column - :likes. In order to sort the websites (html resource, phx.gen) I can easily

  def list_websites(_params) do
    query = from(w in Websites, order_by: [desc: w.likes])
    Repo.all(query)
  end

If I want to render my websites in a given tag I do:

# tag_controller.ex

  def show(conn, %{"id" => id}) do
    tag = Categories.get_tag!(id)
    websites = tag.websites |> Repo.preload(:tags) # Many_to_many
    render(conn, "show.html", tag: tag, websites_for_tag: websites)
  end

The problem is that I can’t figure it out how to order_by the filtered by a tag websites (websites = tag.websites) by :likes. In my context, I have tried:

# Categories.ex

  def list_websites(tag_id) do
    filter = from(wt in WebsiteTag, where: wt.tag_id == ^tag_id and wt.websites_id == websites_id)
    query = from(w in Websites, where: w.websites_id == ^websites_id, order_by: [desc: w.likes])
    Repo.all(query)
  end

# The WebsiteTag schema is where many_to_many websites-tag are stored, my website :likes are in Website schema

with this in the controller:

# tag_controller.ex
  
  def show(conn, %{"id" => id}) do
    tag = Categories.get_tag!(id)
    tag_id = tag.id
    websites = Categories.list_websites(tag_id)
    # websites = tag.websites |> Repo.preload(:tags)
    render(conn, "show.html", tag: tag, websites_for_tag: websites)
  end

But it did not worked… I also tried the order in the controller:

# tag_controller.ex

(...)

websites = tag.websites
filter = from(w in websites, order_by: [desc: w.likes])
 filtered_and_ordered_websites = Repo.all(filter)
               |> Repo.preload(:tags)
render(conn, "show.html", tag: tag, filtered_and_ordered_websites: websites)

But I got an error that protocol Ecto.Queryable is not implemented for Websites.

What should be done, any thoughts?

Best Regards,
ykostov

1 Like
2

It turned out that I hadn’t seen this post for has_many. For me the solution is:

# in tag's context:

  def get_tag!(id) do
    Repo.get!(Tag, id)
    |> Repo.preload([websites: (from w in Websites, order_by: [desc: w.likes])])
  end