Ecto query for list of items' ids

benonymus

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

13 comments

#ecto #ecto-query

2 12448 13

2019-05-08 21:25:54 UTC

Showing Posts 1 to 10

Show Best Posts
Show All (oldest first)
Show All (newest first)

idi527

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

Post #1

benonymus

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]}

Post #2

LostKobrakai

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

Post #3

idi527

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

Post #4

benonymus

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

Post #5

idi527

Ok, what’s inside of team_members?

Post #6

benonymus

[
  %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

Post #7

benonymus

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]

Post #8

benonymus

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

Post #9