Ecto Query

Phoenix Forum Questions / Help
,
1

I have a meeting and picture table.

has_many :pictures, MyApp.Storage.Picture
belongs_to :meeting, MyApp.Listings.Meeting

Picture has a meeting_id

Now I want to render all meeting entries. Some have pics, some haven’t.

My query is:

q = from m in Meeting,
 join: p in assoc(m, :pictures),
 order_by: {:desc, :inserted_at},
 preload: [pictures: p]

meetings = Repo.all(q)

But the result gives only the meetings with pictures.
join: p in Picture…gives to each entry all pictures…

How can I join the picture table, correctly?
Do I have to group?
Thanks for support.

2

So what you’re experiencing is that join performs an INNER JOIN which returns all the rows which have an associated row in the joined table. Replace join with left_join, and you should get the expected result.

https://hexdocs.pm/ecto/Ecto.Query.html#join/5 has more information on the topic

3 Likes
3

Great thanks

4

Same for the show action:

def show(conn, %{"id" => id}) do

meeting = from m in Meeting, where: m.id == ^id,
          left_join: p in assoc(m, :pictures),
          preload: [pictures: p]

render(conn, "show.html", meeting: meeting)

No success…
How to join the pictures table correctly?
Thanks for supporting…

5

Left join shouldn’t be needed for preloads. Ecto will use the association info in the schema and fetch the related data in a second query.

6

This is my solution so far

q = from m in Meeting, where: m.id == ^id,
         join: p in assoc(m, :pictures),
         preload: [pictures: p]

meeting = Repo.one(q)
7

What you are doing looks correct to me. I tested it locally, and it works as expected. What problem are you experiencing?

8

Well… I have to take:

q = from m in Meeting, where: m.id == ^id,
         left_join: p in assoc(m, :pictures),
         preload: [pictures: p]

meeting = Repo.one(q)

left_join is needed, otherwise if the connected table is empty I get an error!

9

So, sorry for not understanding. But what is the problem right now? Left join is what you want to use in your situation. Or, as @mbuhot mentions, just preload without doing a join.

10

As I understand that:

I have one “meeting” which has one or more pictures(or not)
If I show “meeting” i have to get the attached pics via pictures.meeting_id
left_join: p in assoc(m, :pictures), doing that?

Is my understanding right?

11

Is my understanding right?

Yes that is correct.

A Visual Explanation of SQL Joins is a great explanation of how the different types of joins work in SQL.

So LEFT JOIN keeps all the records form the “left” table no matter if any data exists for the columns in the table being joined.

12

Thank for the great link.

1 Like