Ecto query for list of items' ids

Questions / Help
,
1

Hey I am returning a list of users from a function, and I want to use that list for a query.
how could I use the ids of those items for the query selection?

  def get_recording_list_by(team_members) do
recording_query =
  from(
    r in Recording,
    preload: [:user],
    where: r.user_id == ^team_members.user.id, --- questionable part
    select: r
  )

Repo.all(recording_query)
end
2 Likes
2

:wave:

Try

def get_recording_list_by(team_members) do
team_members_ids = Enum.map(team_members, fn team_member -> team_member.user.id end)
recording_query =
  from(
    r in Recording,
    preload: [:user],
    where: r.user_id in ^team_members_ids,
    select: r
  )

Repo.all(recording_query)
end
3

it gives an error that says:
key :user not found in: %Userteam1.Web.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">

Actually inside of this there is the id

%Userteam1.Web.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">, comment: #Ecto.Association.NotLoaded<association :comment is not loaded>, id: 1, inserted_at: ~N[2018-09-02 07:41:17.708299], name: "bencettx", password: nil, password_hash: "$2b$12$gH9OMPfte1W4wyn.1kHu..JEYcOCnn9CtQWRr9UI3hrLXqrzRcPea", recording: #Ecto.Association.NotLoaded<association :recording is not loaded>, role: #Ecto.Association.NotLoaded<association :role is not loaded>, role_id: 1, score: 6, team: #Ecto.Association.NotLoaded<association :team is not loaded>, team_id: 1, updated_at: ~N[2018-09-02 09:30:44.993189]}

4

Ecto.assoc(team_members, :recording) if you have the association set up both ways.

5

Then team_members_ids = Enum.map(team_members, fn team_member -> team_member.id end)

6

it says this:

(Protocol.UndefinedError) protocol Enumerable     not implemented for #Ecto.Query<from r in Userteam1.Web.Recording, where: r.user_id == ^1>. This protocol is implemented for: DBConnection.PrepareStream, DBConnection.Stream, Date.Range, Ecto.Adapters.SQL.Stream, File.Stream, Function, GenEvent.Stream,
HashDict, HashSet, IO.Stream, List, Map, MapSet, Postgrex.Stream, Range, Stream
7

:cold_sweat:

Ok, what’s inside of team_members?

8 
[
  %Userteam1.Web.User{
    __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
    comment: #Ecto.Association.NotLoaded<association :comment is not loaded>,
    id: 1,
    inserted_at: ~N[2018-09-02 07:41:17.708299],
    name: "bencettx",
    password: nil,
    password_hash: "$2b$12$gH9OMPfte1W4wyn.1kHu..JEYcOCnn9CtQWRr9UI3hrLXqrzRcPea",
    recording: #Ecto.Association.NotLoaded<association :recording is not loaded>,
    role: #Ecto.Association.NotLoaded<association :role is not loaded>,
    role_id: 1,
    score: 6,
    team: #Ecto.Association.NotLoaded<association :team is not loaded>,
    team_id: 1,
    updated_at: ~N[2018-09-02 09:30:44.993189]
  }
]

it printed this out after the first run, but doesnt really print out anything now

9

OK something new, I commented out the ecto.assoc
and now this is the error at the end:

lib/userteam1_web/controllers/recording_controller.ex:30: value `[1]` in `where` cannot be cast to type :id in query:
from r in Userteam1.Web.Recording,
  where: r.user_id == ^[1],
  select: r,
  preload: [:user]
10

fixed it by using in instead of ==
so the final code looks like this:

def get_recording_list_by_team_id(team_members) do
    IO.inspect(team_members)
    team_members_ids = Enum.map(team_members, fn team_member -> team_member.id end)
    IO.inspect(team_members_ids)

    recording_query =
      from(
        r in Recording,
        preload: [:user],
        where: r.user_id in ^team_members_ids,
        select: r
      )

    Repo.all(recording_query)
  end
3 Likes
11

Or just recording_query = from Ecto.assoc(team_members, :recording), preload: [:user]

4 Likes
12

Can anybody point to me where this IN is documented in ecto? I cannot find, and it took me so long to find this post :frowning:

1 Like
13

https://hexdocs.pm/ecto/Ecto.Query.API.html#in/2

PostgreSQL IN comparison

though you’re possibly talking about from p in Post

https://hexdocs.pm/ecto/Ecto.Query.html#from/2

In general the in expression is looking for an Ecto.Queryable on the right hand side - if it’s a string it is referring to a DB table. The Ecto.Queryable is usually a module that uses Ecto.Schema

2 Likes
14

I’m glad my post helped you as well as it helped me :grin:

1 Like