Missing query results with many_to_many mapping with Join schema

[anandtrex]

Issue

If I have two schemas that are related through a many_to_many relationship using a Join schema, I do not get the expected number of results (I get too few values) when I query the database with rethinkdb_ecto to retrieve all items associated with one item of the other schema. I have verified that the join schema table itself contains the right data, and queries to rethinkdb directly using eqJoin give the expected number of results.

I'm using phoenix 1.3.0, elixir 1.5.1, erlang/OTP 20 on Archlinux with rethinkdb 2.3.6.

Detailed steps to reproduce

I have two schemas like so:

defmodule TestRethink.Post do
  use Ecto.Schema
  import Ecto.Changeset
  alias TestRethink.Post

  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id
  schema "posts" do
    field :content, :string
    field :title, :string
    many_to_many :tags, TestRethink.Tag, join_through: TestRethink.PostTag, on_delete: :delete_all
    timestamps()
  end

  @doc false
  def changeset(%Post{} = post, attrs) do
    post
    |> cast(attrs, [:title, :content])
    |> validate_required([:title, :content])
  end
end

defmodule TestRethink.Tag do
  use Ecto.Schema
  import Ecto.Changeset
  alias TestRethink.Tag

  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id
  schema "tags" do
    field :name, :string
    many_to_many :posts, TestRethink.Post, join_through: TestRethink.PostTag, on_delete: :delete_all
    timestamps()
  end

  @doc false
  def changeset(%Tag{} = tag, attrs) do
    tag
    |> cast(attrs, [:name])
    |> validate_required([:name])
  end
end

which have a many-to-many mapping between each other using an intermediate schema (like in the official example:

defmodule TestRethink.PostTag do
  use Ecto.Schema
  import Ecto.Changeset
  alias TestRethink.PostTag

  @primary_key false
  @foreign_key_type :binary_id
  schema "posts_tags" do
    belongs_to :post, Post
    belongs_to :tag, Tag
    timestamps()
  end

  @doc false
  def changeset(%PostTag{} = post_tag, attrs) do
    post_tag
    |> cast(attrs, [:post_id, :tag_id])
    |> validate_required([:post_id, :tag_id])
  end
end

Now I put 150 items in :posts, all of which are associated with, say, one item in :tags like so:

# Insert 150 Posts
posts = 1..150  |> Enum.map(fn _ -> 
  Post.changeset(%Post{}, %{title: "Some Title", content: "This is a sentence"}) 
  |> Repo.insert! 
end)

# Insert 1 tag
tag = Tag.changeset(%Tag{}, %{name: "Shakespeare"}) |> Repo.insert!

# Insert associations between all the posts and the one tag
Enum.map(posts, 
         fn post -> PostTag.changeset(%PostTag{}, %{post_id: post.id, tag_id: tag.id})
         |> Repo.insert! 
         end)

Now, when I try to retrieve all the posts associated with the tag:

tag = hd Repo.all(Tag)
posts = Repo.all(assoc(tag, :posts))

I do not get back 150 posts as expected, but much fewer. In fact the exact number of posts I get back varies from time to time, and is around 40-50.

[redrabbit]

I did investigate a little bit on this and found out that many_to_many require more complex handling of joins via the adapter. Don't have time to fix this right now but any help is welcome.

In the meantime, you might want to stick to has_many or embeds_many.