Multiple nested filters to get specific values

[Sardoan]

Hi,
I got the following situation: I have multiple chats, wanted to get in my preload the newest message for each chat, but limit: 1 gives only one message, not one for each chat. So I thought about filtering my structure after the preload is done.
Here my code:

result
|> Enum.filter(fn(x) ->
      Enum.filter(x.chat_users, fn(y) ->
        case y == Enum.at(x.chat_users, 0) do
          true ->
            Enum.filter(y.chat_messages, fn(z) ->
              case z == Enum.at(y.chat_messages, 0) do
                true -> true
                false -> false
              end
            end)
          false ->  false
        end
      end)
     true
   end)

In my preload I ordered the chat_messages and chat_users desc, so Enum.at(0) are the newest ones.
I know the inner filter functions have no effect on the outer scope. I know, looks really ugly and is not working.

In simple words I have my result with chats and it has chat_users and each chat_user has chat_messages and i need for each chat the newest user (Enum.at(0)) and the newest chat_message for this user.

How to accomplish this the elixir way? I don’t know how to use reduce inside my nested structure.

THX

[idi527]

You can probably use a lateral join to preload the newest message for each chat.

Preloading top comments for posts in Ecto

Maybe https://hexdocs.pm/ecto/Ecto.Query.html#join/5-joining-with-fragments would help. I guess SELECT comments.* FROM posts JOIN LATERAL ( SELECT * FROM comments WHERE comments.posts_id = posts.id ORDER BY score DESC LIMIT 3 ) comments ON true WHERE posts.id IN (1, 2, 3) ORDER BY posts.id; might be rewritten with ecto as defmacrop last_comments(post_id, limit \\ 3) do quote do fragment( "SELECT * FROM comments WHERE comments.post_id = ? ORDER BY score DESC LIMIT ?", …

---

As for your code snippet, it might be simplified to

%{chat_users: chat_users} = result

Enum.map(chat_users, fn
  %{chat_messages: [first_message | _rest]} = chat_user -> {chat_user, first_message}
  chat_user -> {chat_user, nil}
end)

It would return a list of user/message tuples with the latter possibly being nil.

[Sardoan]

Hmmm,

here is what I am using as preload:

defp preload_overview_associations(chat, params) do
  updated_since = Map.get(params, "updated_since", "1970-01-01T12:00:00")
  chat_user_query = from cu in Chat.ChatUser,
  where: cu.updated_at > ^updated_since,
  order_by: [desc: cu.updated_at]

  user_query = from u in User,
  where: u.deleted == false

  chat_message_query = from m in Chat.ChatMessage,
  where: m.updated_at > ^updated_since,
  order_by: [desc: m.updated_at]

Repo.preload(chat, [
  [group:
  [[picture: LimitedVariants.preload_expr(params)],
  [users_groups:
    [user: [avatar: LimitedVariants.preload_expr(params)]]]]],
  [chat_users:
    {chat_user_query,
    [
      [user: {user_query, [avatar: LimitedVariants.preload_expr(params)]}],
      [chat: [picture: LimitedVariants.preload_expr(params)]],
      [chat_messages:
        {chat_message_query,
        [[picture: LimitedVariants.preload_expr(params)], :audio, :video, :attachment]}
      ]
    ]}
  ],
  [picture: LimitedVariants.preload_expr(params)]
 ])
end

To be honest, no idea how to add a lateral join to my existing stuff

[idi527]

Try to write it as SQL, it usually makes things simpler for me. Maybe refactor that function into functions / multis as well while at it.

You can probably replace

  chat_message_query = from m in Chat.ChatMessage,
  where: m.updated_at > ^updated_since,
  order_by: [desc: m.updated_at]

with a lateral join on chat_user_query.

[Sardoan]

I think it is much easier to modify the result after the existing preload. Would take me 5 minutes in languages like C#, JS, Java… so should be also very easy for the elixir pros.

[idi527]

I’ve posted a possible function above …

[Sardoan]

Never used a lateral join. Need to check first, how to do it. Still reading https://medium.com/kkempin/postgresqls-lateral-join-bfd6bd0199df

[idi527]

@idi527

%{chat_users: chat_users} = result Enum.map(chat_users, fn %{chat_messages: [first_message | _rest]} = chat_user -> {chat_user, first_message} chat_user -> {chat_user, nil} end)

I meant this function.

For a lateral join, just replace chat_message_query with it.

[Sardoan]

Ah, I checked the wrong answer. Looked at the one above, not the one before. THX, will try it.

[Sardoan]

I chose to remove the data after the preload:

|> Enum.map(fn chat ->
  Map.update!(chat, :chat_users, fn chat_users ->
    case Enum.at(chat_users, 0) do
      nil -> nil
      _ ->
        chat_user =
        chat_users
        |> List.first
        |> Map.update!(:chat_messages, & [List.first(&1)])
      [chat_user]
    end
  end)
end)

Seems I still need more practice in Elixir

[idi527]

|> Enum.map(fn
  %{chat_users: [%{chat_messages: chat_messages} = first_chat_user | _rest]} = chat ->
    %{chat | chat_users: [%{first_chat_user | chat_messages: List.first(chat_messages)}]}
  other ->
    other
end)

does the same thing but in a bit more declarative way.

[Sardoan]

And looks even better THX