Search code examples
elixirphoenix-frameworkecto

"many_to_many" if one of the foreign keys is unique string

I have 2 main tables: articles and tags. They have "many_to_many_ relationship via articles_to_tags table. A thing is that a key of tags via which articles and tags are connected is of type string. On the level of database they look as follows:

tags:
  - id
  - name (unique)

articles
  - id
  - title
  - body

articles_to_tags:
  - article_id
  - tag_name /* !! */

On the level of Ecto:

  # articles_to_tags


  @primary_key false
  schema "articles_to_tags" do
    belongs_to(:article, Article)
    belongs_to(:tag, Tag, references: :name, foreign_key: :tag_name, type: :string)
  end

Even though I've filled out a database with test data that's assigns some tags to an article, in my project an article still has 0 tags:

article = Repo.get(Article, 123) |> Repo.preload(:tags)
IO.puts("*** article tags len: #{length(article.tags)}")

Why?

I've tried to play with join_keys, and it's turned out to be confusing and hasn't resolved the issue:

schema "articles" do
    # ................

    many_to_many(:tags, Tags, join_through: ArticleToTag,
      join_keys: [
        tag_name: :name,         # 'name' of which table?
        article_id: :id          # 'id' of which table?
      ]
    )

How to fix it?

Joining the tables via "tag.name" instead of "tag.id" is a requirement.

Solution

  • From the docs: https://hexdocs.pm/ecto/Ecto.Schema.html#many_to_many/3

    :join_keys - ..,expects a keyword list with two entries, the first being how the join table should reach the current schema and the second how the join table should reach the associated schema.

    So you should try doing this in your Articles schema:

    schema "articles" do
    # ................

    many_to_many(:tags, Tags, join_through: ArticleToTag,
      join_keys: [
        article_id: :id          # 'id' of current schema
        tag_name: :name,         # 'name' of associated schema
      ]
    )