Based on your tables, I would have this schema:
defmodule MyQuery.Post do
use MyQuery.Web, :model
schema "posts" do
field :title, :string
field :body, :string
many_to_many :tags, MyQuery.Tag, join_through: "posts_tags"
timestamps()
end
end
defmodule MyQuery.PostTag do
use MyQuery.Web, :model
schema "posts_tags" do
belongs_to :post, MyQuery.Post
belongs_to :tag, MyQuery.Tag
timestamps()
end
end
defmodule MyQuery.Tag do
use MyQuery.Web, :model
schema "tags" do
field :name, :string
many_to_many :posts, MyQuery.Post, join_through: "posts_tags"
timestamps()
end
end
With 2 posts, 3 tags, and post1 associated with tags 1 & 2 and post 2 associated with tags 2 & 3:
iex(15)> Repo.all(Post)
[debug] QUERY OK source="posts" db=1.2ms
SELECT p0."id", p0."title", p0."body", p0."inserted_at", p0."updated_at" FROM "posts" AS p0 []
[%MyQuery.Post{__meta__: #Ecto.Schema.Metadata<:loaded, "posts">,
body: "Body Text", id: 1, inserted_at: ~N[2017-05-12 02:45:40.606380],
tags: #Ecto.Association.NotLoaded<association :tags is not loaded>,
title: "Title1", updated_at: ~N[2017-05-12 02:45:40.606387]},
%MyQuery.Post{__meta__: #Ecto.Schema.Metadata<:loaded, "posts">,
body: "Body Text", id: 2, inserted_at: ~N[2017-05-12 02:46:01.298796],
tags: #Ecto.Association.NotLoaded<association :tags is not loaded>,
title: "Title2", updated_at: ~N[2017-05-12 02:46:01.298802]}]
iex(16)> Repo.all(Tag)
[debug] QUERY OK source="tags" db=0.8ms
SELECT t0."id", t0."name", t0."inserted_at", t0."updated_at" FROM "tags" AS t0 []
[%MyQuery.Tag{__meta__: #Ecto.Schema.Metadata<:loaded, "tags">, id: 1,
inserted_at: ~N[2017-05-12 02:44:03.558857], name: "Sports",
posts: #Ecto.Association.NotLoaded<association :posts is not loaded>,
updated_at: ~N[2017-05-12 02:44:03.567081]},
%MyQuery.Tag{__meta__: #Ecto.Schema.Metadata<:loaded, "tags">, id: 2,
inserted_at: ~N[2017-05-12 02:44:03.588083], name: "News",
posts: #Ecto.Association.NotLoaded<association :posts is not loaded>,
updated_at: ~N[2017-05-12 02:44:03.588089]},
%MyQuery.Tag{__meta__: #Ecto.Schema.Metadata<:loaded, "tags">, id: 3,
inserted_at: ~N[2017-05-12 02:44:03.590854], name: "Opinion",
posts: #Ecto.Association.NotLoaded<association :posts is not loaded>,
updated_at: ~N[2017-05-12 02:44:03.590859]}]
iex(17)> Repo.all(PostTag)
[debug] QUERY OK source="posts_tags" db=0.9ms
SELECT p0."id", p0."post_id", p0."tag_id", p0."inserted_at", p0."updated_at" FROM "posts_tags" AS p0 []
[%MyQuery.PostTag{__meta__: #Ecto.Schema.Metadata<:loaded, "posts_tags">, id: 1,
inserted_at: ~N[2017-05-12 02:47:30.915457],
post: #Ecto.Association.NotLoaded<association :post is not loaded>,
post_id: 1, tag: #Ecto.Association.NotLoaded<association :tag is not loaded>,
tag_id: 1, updated_at: ~N[2017-05-12 02:47:30.915462]},
%MyQuery.PostTag{__meta__: #Ecto.Schema.Metadata<:loaded, "posts_tags">, id: 2,
inserted_at: ~N[2017-05-12 02:48:01.578237],
post: #Ecto.Association.NotLoaded<association :post is not loaded>,
post_id: 1, tag: #Ecto.Association.NotLoaded<association :tag is not loaded>,
tag_id: 2, updated_at: ~N[2017-05-12 02:48:01.578242]},
%MyQuery.PostTag{__meta__: #Ecto.Schema.Metadata<:loaded, "posts_tags">, id: 3,
inserted_at: ~N[2017-05-12 02:48:20.769033],
post: #Ecto.Association.NotLoaded<association :post is not loaded>,
post_id: 2, tag: #Ecto.Association.NotLoaded<association :tag is not loaded>,
tag_id: 2, updated_at: ~N[2017-05-12 02:48:20.769039]},
%MyQuery.PostTag{__meta__: #Ecto.Schema.Metadata<:loaded, "posts_tags">, id: 4,
inserted_at: ~N[2017-05-12 02:48:39.862811],
post: #Ecto.Association.NotLoaded<association :post is not loaded>,
post_id: 2, tag: #Ecto.Association.NotLoaded<association :tag is not loaded>,
tag_id: 3, updated_at: ~N[2017-05-12 02:48:39.862816]}]
Here is how I would preload the tags on post1 (you can see the two tags loaded under the tags key):
iex(19)> query = from p in Post, where: p.id == 1, preload: [:tags]
#Ecto.Query<from p in MyQuery.Post, where: p.id == 1, preload: [:tags]>
iex(20)> post1 = Repo.all(query)
[debug] QUERY OK source="posts" db=1.3ms
SELECT p0."id", p0."title", p0."body", p0."inserted_at", p0."updated_at" FROM "posts" AS p0 WHERE (p0."id" = 1) []
[debug] QUERY OK source="tags" db=2.7ms
SELECT t0."id", t0."name", t0."inserted_at", t0."updated_at", p1."id" FROM "tags" AS t0 INNER JOIN "posts" AS p1 ON p1."id" = ANY($1) INNER JOIN "posts_tags" AS p2 ON p2."post_id" = p1."id" WHERE (p2."tag_id" = t0."id") ORDER BY p1."id" [[1]]
[%MyQuery.Post{__meta__: #Ecto.Schema.Metadata<:loaded, "posts">,
body: "Body Text", id: 1, inserted_at: ~N[2017-05-12 02:45:40.606380],
tags: [%MyQuery.Tag{__meta__: #Ecto.Schema.Metadata<:loaded, "tags">, id: 1,
inserted_at: ~N[2017-05-12 02:44:03.558857], name: "Sports",
posts: #Ecto.Association.NotLoaded<association :posts is not loaded>,
updated_at: ~N[2017-05-12 02:44:03.567081]},
%MyQuery.Tag{__meta__: #Ecto.Schema.Metadata<:loaded, "tags">, id: 2,
inserted_at: ~N[2017-05-12 02:44:03.588083], name: "News",
posts: #Ecto.Association.NotLoaded<association :posts is not loaded>,
updated_at: ~N[2017-05-12 02:44:03.588089]}], title: "Title1",
updated_at: ~N[2017-05-12 02:45:40.606387]}]
It looks like you are trying to join and then preload, but your post row is going to be duplicated in the join to match the two tag rows and the tags will be preloaded on the duplicate posts. This may be why you are getting an unexpected result:
iex(21)> query2 = from p in Post, join: t in assoc(p, :tags), where: p.id == 1, preload: [:tags]
#Ecto.Query<from p in MyQuery.Post, join: t in assoc(p, :tags), where: p.id == 1, preload: [:tags]>
iex(22)> post2 = Repo.all(query2)
[debug] QUERY OK source="posts" db=3.5ms
SELECT p0."id", p0."title", p0."body", p0."inserted_at", p0."updated_at" FROM "posts" AS p0 INNER JOIN "posts_tags" AS p2 ON p2."post_id" = p0."id" INNER JOIN "tags" AS t1 ON p2."tag_id" = t1."id" WHERE (p0."id" = 1) []
[debug] QUERY OK source="tags" db=2.6ms
SELECT t0."id", t0."name", t0."inserted_at", t0."updated_at", p1."id" FROM "tags" AS t0 INNER JOIN "posts" AS p1 ON p1."id" = ANY($1) INNER JOIN "posts_tags" AS p2 ON p2."post_id" = p1."id" WHERE (p2."tag_id" = t0."id") ORDER BY p1."id" [[1]]
[%MyQuery.Post{__meta__: #Ecto.Schema.Metadata<:loaded, "posts">,
body: "Body Text", id: 1, inserted_at: ~N[2017-05-12 02:45:40.606380],
tags: [%MyQuery.Tag{__meta__: #Ecto.Schema.Metadata<:loaded, "tags">, id: 1,
inserted_at: ~N[2017-05-12 02:44:03.558857], name: "Sports",
posts: #Ecto.Association.NotLoaded<association :posts is not loaded>,
updated_at: ~N[2017-05-12 02:44:03.567081]},
%MyQuery.Tag{__meta__: #Ecto.Schema.Metadata<:loaded, "tags">, id: 2,
inserted_at: ~N[2017-05-12 02:44:03.588083], name: "News",
posts: #Ecto.Association.NotLoaded<association :posts is not loaded>,
updated_at: ~N[2017-05-12 02:44:03.588089]}], title: "Title1",
updated_at: ~N[2017-05-12 02:45:40.606387]},
%MyQuery.Post{__meta__: #Ecto.Schema.Metadata<:loaded, "posts">,
body: "Body Text", id: 1, inserted_at: ~N[2017-05-12 02:45:40.606380],
tags: [%MyQuery.Tag{__meta__: #Ecto.Schema.Metadata<:loaded, "tags">, id: 1,
inserted_at: ~N[2017-05-12 02:44:03.558857], name: "Sports",
posts: #Ecto.Association.NotLoaded<association :posts is not loaded>,
updated_at: ~N[2017-05-12 02:44:03.567081]},
%MyQuery.Tag{__meta__: #Ecto.Schema.Metadata<:loaded, "tags">, id: 2,
inserted_at: ~N[2017-05-12 02:44:03.588083], name: "News",
posts: #Ecto.Association.NotLoaded<association :posts is not loaded>,
updated_at: ~N[2017-05-12 02:44:03.588089]}], title: "Title1",
updated_at: ~N[2017-05-12 02:45:40.606387]}]
Thanks for the help! I’m still very new to this. 
