Ecto query on a map field using fragments

Questions / Help
#1

Hello all!

I am trying to write a macro + function to query on a field which is a map. Here is the schema:

schema "documents" do
    field :content, :string
    field :rendered_content, :string
    field :title, :string
    field :author_id, :integer
    field :attributes, :map

And here is the code in module Query that I am struggling to write:

  defmacro has_attribute_aux(key, value) do
    quote do
      fragment("SELECT id FROM documents WHERE attributes @> '{?: ?}'", unquote(key), unquote(value))
    end
  end

  def has_attribute(query, key, value) do
    from d in query,
      where: has_attribute_aux(^key, ^value)
  end

With it I would like to say

 Document |> Query.is_public |> Repo.all

This way I would be able to compose with other queries that do work, as in

 Document |> Query.has_author(2) |> Query.is_public |> Repo.all

Alas, I have no experience with either fragments or defining macros …

#2

You haven’t exactly specified what is the problem - is there an error, is there something not working as expected?

Looking at the code, I see one thing - you can use parameters in queries as part of strings. You do that in '{?:?}' part. All postgres will see there is a string with question marks (actually dollars since it’s later converted by the adapter) - the point is, it won’t interpret this as a parameter. You either need to use postgres string concatenation (||) or use other operators than @>.

#3

You want the fragment to contain only a fragment of the whole query, so it should rather be:

fragment("attributes @> '{?: ?}'", unquote(key), unquote(value))

But the best would probably be:

fragment("? @> '{?: ?}'", unquote(field), unquote(key), unquote(value))

And call it as:

import Query
from d in Document, where:  has_attribute(d.attributes, ^key, ^value)
#4

Thankyou very much!