Getting an error when joining tables... not sure when to use joins vs preloads

Phoenix Forum Questions / Help
1

Hello,

I have an app with user login system created with phx.gen.auth. There is a router plug which uses fetch_current_user() function to fetch user on every load. Every user also has a profile in another table so when a user loads, it also preloads its profile.

user_auth.ex
def fetch_current_user(conn, _opts) do
    {user_token, conn} = ensure_user_token(conn)
    user = user_token && Accounts.get_user_by_session_token(user_token)
    user = Repo.preload(user, [:profile])

    conn
    |> assign(:current_user, user)
  end

user.ex
schema "users" do
   ...fields...

   has_one :profile, Profile

end

Profiles doesn’t have id field, instead they use user_id as primary key.

profile.ex
@primary_key {:user_id, :integer, autogenerate: false}
  schema "profiles" do
    field :name, :string
   ...other fields

    embeds_one :image, ProfileImageEmbed, on_replace: :update

    belongs_to :user, User, define_field: false

    timestamps()
end

user_token.ex
def verify_session_token_query(token) do
    query =
      from token in token_and_context_query(token, "session"),
        join: user in assoc(token, :user),
        where: token.inserted_at > ago(@session_validity_in_days, "day"),
        select: user

    {:ok, query}
  end

This code works as expected but every time a page loads, it creates two queries, one for user_token which joins a user to user_token, and another query to preload user profile.
I would like to optimize this and use only one query to get both the user and their profile. I think I should use join to do that. I’m a beginner and I have a little bit of problem understanding when exactly should I use joins and when should I use preloads so if someone could give me some general guidance when is one better then the other, I would be very grateful.

user_token.ex
def verify_session_token_query(token) do
    query =
      from token in token_and_context_query(token, "session"),
        join: user in assoc(token, :user),
        join: profile in assoc(user, :profile),
        where: token.inserted_at > ago(@session_validity_in_days, "day"),
        select: {user, profile.name, profile.image}

    {:ok, query}
  end

So, I removed profile preload from fetch_current_user() and added join to user.token.ex. For some reason I’m getting an error.

key :id not found in: {#MyApp.Accounts.User<__meta__: #Ecto.Schema.Metadata<:loaded, "users">, id: 1, , email: ....

Can someone help me with this?

2

The query is returning a tuple. You have to pattern match for user and then access :id on user struct.

{user, name, image} = result
#access user
user.id

Earlier verify_session_token_query was returning user, now it is returning tuple with 3 elements {user, name, image}.

Edit: fixed some typos

1 Like
3

Typically Ecto.preload is best for when you have an Ecto Struct that that you may did not query say in you current function or flow of execution, so essentially for one-time uses
Usually, I use Ecto.Query’s :preload. So for your first query, it was possible to actually achieve this in user_auth.ex inside the Accounts.get_user_by_session_token via such a query

user = from u in User, preload: [:profile], where u.id == ^user.id

As mentioned above when you used the :select , you are no longer returning a singular map but a tuple, so pattern map appropriately.

1 Like
4

Your link to goes to an article about Go on Medium?

Just adding preload to the query does not reduce the number of queries.
Reworking your query from above (not tested):

from user in User,
        join: token in token_and_context_query(token, "session"),
        on: token.user_id == user.id,
        join: profile in assoc(user, :profile),
        where: token.inserted_at > ago(@session_validity_in_days, "day"),
        preload: [profile: profile]

This should return a user with the profile loaded in a single query.

1 Like
5

Thanks for pointing out the bad link.

I can confirm that indeed inlining the preload bundles the preload in the same query.
https://hexdocs.pm/ecto/Ecto.Query.html#preload/3

6

Update on the preload, after reading the docs keely, in order to make the request quick is to use preload with a join this actually results in only a single query. @Hermanverschooten thanks for pointing that out, I completely skimmed through the documentation on the dits.
for example

Repo.all from p in Post,
           join: c in assoc(p, :comments),
           where: c.published_at > p.updated_at,
           preload: [comments: c]