Protocol Ecto.Queryable not implemented for Item of type List

Questions / Help
,
1

Background

I have an List of metadata items that has the following format:

[%{id: "id1"}, %{id: "id2"}, %{id: "id3"}]

I am trying to do a query using a join with this list, the idea here is to pick posts who have this specific metadata ids.

relevant_metadata_ids = [%{id: "id1"}, %{id: "id2"}, %{id: "id3"}] 

posts =
      Post
      |> join(:inner, [post], meta in ^relevant_metadata_ids,
        on: fragment("?->>'meta_id'", post.metadata) == meta.id
      )

Problem

However, it seems I can’t quite do this, as I get the following error:

** (Protocol.UndefinedError) protocol Ecto.Queryable not implemented for [%{meta_upload_id: “id1”}] of type List. This protocol is implemented for the following type(s): Atom, BitString, Ecto.Query, Ecto.SubQuery, Tuple

Question

Is there a way to leverage the information I have in the list and use it in an Ecto Query?

2

Generally I’d start by looking at the databases capabilities. Ecto cannot invent functionality the db doesn’t support.

Generally you can only join tablelike structures in sql, where the lines are considered rows. The easiest way to represent them in sql is VALUES lists, but those are not supported by ecto at the moment (at least not with dynamic content).

Then there are various db specific functions to turn various shapes of data into rows. For postgres there’s for example unnest (which I’ve used here) or my more recent favorite jsonb_to_recordset: Postgres VALUES in query - #2 by LostKobrakai

3

I have found a way around my problem. If using join is not a viable option, then perhaps I can use another construct. This is the case for where.

By changing the list List of metadata items to just the ids:

[%{id: "id1"}, %{id: "id2"}, %{id: "id3"}] 
# becomes
["id1", "id2", "id3"]

I can then transform my query to use a where clause:

relevant_metadata_ids = ["id1", "id2", "id3"]

posts =
      Post
      |> where([post], fragment("?->>'meta_id'", post.metadata) in ^relevant_metadata_ids)
      |> ...

This works as it seems to be quite fast. If the ids inside of relevant_metadata_ids are not repeated, its even better.

I will eventually have to join with a correspondent table though, but this does the job.

4

So it’s mad because it doesn’t do lists. Here are your options:

  • an Ecto.Schema, such as p in Post
  • an interpolated Ecto query with zero or more where clauses, such as c in ^(from "posts", where: [public: true])
  • an association, such as c in assoc(post, :comments)
  • a subquery, such as c in subquery(another_query)
  • a query fragment, such as c in fragment("SOME COMPLEX QUERY"), see “Joining with fragments” below.

A subquery could maybe work like this:

metadata_query = from(meta in Metadata, where: meta.id in ^metadata_ids)

Post |> 
                   join(:inner, [post], meta in subquery(metadata_query), 
                   on: fragment("?->>'meta_id'", post.metadata) == meta.id)
5

As I mentioned in my solution, I ended up using a where clause.
However, it is important to know this solution has an important drawback. Psql has a soft limit for the number of parameters it can handle (usually it is MAX_INT for the system, so it is either 32K or 64K). If your application is passing a list with more than 32K or 64K (depending on system) parameters to the where clause, the query will fail.

This is not my case. So I use it.