Please help me write this query in Ecto

Questions / Help
1

I have this schema:

 schema "message_threads" do
    field :title, :string
    field :is_group, :boolean, default: false
    has_many :messages, Platform.Social.Message
    many_to_many :users, Platform.Accounts.User, join_through: "users_message_threads", unique: true
    timestamps()
  end

I have two user id’s, uid1, uid2

I want to:
Select all message threads for which user with id: uid1 AND user with uid2 are both associated

2

My last reply was about the same issue → Ecto query many to many associations that match all elements of a list, but your query will be a little bit simpler. Did you get the idea?

3

Thanks, I’m still struggling with this though.

I thought that I might be able to skip querying the association table directly, and use assoc() somehow, but maybe not.

Unlike your example, my having clause can’t use an aggregate function, and instead needs to make sure that the users_message_threads has both of the user ids.

I’ve tried this:

    query =
      from u in User,
        join: umt in "users_message_threads",
        on: umt.user_id == u.id,
        where: u.id in ^[user1_id, user2_id],
        select: umt.message_thread_id,
        group_by: umt.message_thread_id,
        having: ^user1_id in umt and ^user2_id in umt

which is giving me this error (user ids are binary/uuids)

** (exit) an exception was raised:
    ** (Postgrex.Error) ERROR 42809 (wrong_object_type) op ANY/ALL (array) requires array on right side

    query: SELECT u1."message_thread_id" FROM "users" AS u0 INNER JOIN "users_message_threads" AS u1 ON u1."user_id" = u0."id" WHERE (u0."id" = ANY($1)) GROUP BY u1."message_thread_id" HAVING ($2 = ANY(u1) AND $3 = ANY(u1))

I’ve also tried this query:

    usr1_query =
      from umt in "user_message_threads",
        where: umt.user_id == ^user1_id,
        select: umt.message_thread_id

    umts =
      from umt in "user_message_threads",
        where: umt.user_id == ^user2_id,
        select: umt.message_thread_id,
        intersect_all: ^usr1_query

    query =
      from mt in MessageThread,
        where: mt.id in ^umts and mt.is_group == false

which is giving me:

[debug] ** (Ecto.Query.CastError) lib/platform/social.ex:111: value `#Ecto.Query<from u0 in "user_message_threads", where: u0.user_id == ^"4b3c421e-4e50-4643-8762-d064ac49d946", intersect_all: (from u0 in "user_message_threads",
  where: u0.user_id == ^"0fa4ade7-691b-4333-a48e-2044f2925d3f",
  select: u0.message_thread_id), select: u0.message_thread_id>` in `where` cannot be cast to type {:in, :binary_id} in query:
4

Try with having: count() == 2.

Your code with operator in should work if you use ARRAY_AGG aggregate function.

having: ^user1_id in fragment("ARRAY_AGG(?)", umt.message_thread_id)
    and ^user2_id in fragment("ARRAY_AGG(?)", umt.message_thread_id)
1 Like
5

ok that works for me: Thanks!
I had to cast to binary id though.

    query =
      from u in User,
        join: umt in "users_message_threads",
        on: umt.user_id == u.id,
        where: u.id in ^[user1_id, user2_id],
        select: umt.message_thread_id,
        group_by: umt.message_thread_id,
        having:
          count() == 2 and
            type(^user1_id, :binary_id) in fragment("array_agg(?)", umt.user_id) and
            type(^user2_id, :binary_id) in fragment("array_agg(?)", umt.user_id)

will it build the array_agg twice though?

6

I guess, you can leave just count == 2 without duplication the logic in further conditions.

will it build the array_agg twice though?

use @> or <@ to be sure :slight_smile:

having: fragment("? <@ array_agg(?)", ^[user1_id, user2_id], umt.user_id)
2 Likes