how do I join in an Repo.find_by() in elixir

Question

I need to look up a user, but also join it with another table (partner). Here's the working code without the join:

result = Repo.get_by(User, login: auth.info.email)

The user table has a foreign key with the partner table, so I thought to try this:

result = Repo.get_by(User, %{ login: auth.info.email, join: :partner } )

But this results in:

field `User.join` in `where` does not exist in the schema in query:

so it's obviously taking the join as a column name. I don't want to preload, because - as I understand - this will load the entire table in memory, and it can get big, so I need a join at the database level.

Answer 1

I don't want to preload, because - as I understand - this will load the entire table in memory, and it can get big, so I need a join at the database level.

Preloading does not load the whole table in memory, but only the records where the specific foreign key matches the given struct's id.

Assuming :partner is either a belongs_to or has_many or has_one relationship of User, you can load it like this:

user = Repo.get_by!(User, login: auth.info.email) |> Repo.preload(:partner)

If you're relying on Repo.get_by to return a nil on no record found, you'll need to handle that case yourself since Repo.preload will raise an error if you pass it nil as the first argument:

user = if u = Repo.get_by(User, login: auth.info.email), do: Repo.preload(u, :partner), else: nil

After either of these, you can access partner as user.partner.