Type `tsquery` can not be handled by the types module Ecto.Adapters.Postgres.TypeModule

Questions / Help
,
1

The problem is that I don’t think I am returning tsquery anywhere:

  defmacro to_tsquery(query) do
    quote do
      fragment("ts_rewrite(to_tsquery('english', ?), 'select t,s from aliases')", unquote(query))
    end
  end

  defmacro ts_rank_cd(tsv, query) do
    quote do
      fragment("ts_rank_cd(?, ?)", unquote(tsv), unquote(query))
    end
  end

  @spec search(User.id, binary) :: [DB.File.t]
  def search(user_id, query) do
    query = pre_parse(query)

    User
    |> where(id: ^user_id)
    |> join(:inner, [u], us in "users_subjects", u.id == us.user_id)
    |> join(:inner, [u, us], s in Subject, s.id == us.assoc_id)
    |> join(:inner, [u, us, s], f in DB.File, f.subject_id == s.id)
    |> where([u, us, s, f], fragment("? @@ ?", f.tsv, to_tsquery(^query)))
    |> select([u, us, s, f], f)
    |> order_by([u, us, s, f], desc: ts_rank_cd(f.tsv, ^query)) # <-- without this it works
    |> limit(28)
    |> Repo.all()
  end

Generated SQL (simplified, removed all columns other than name)

SELECT i3."name"
FROM "users" AS u0
INNER JOIN "users_subjects" AS u1 ON u0."id" = u1."user_id"
INNER JOIN "subjects" AS s2 ON s2."id" = u1."assoc_id"
INNER JOIN "files" AS i3 ON i3."subject_id" = s2."id"
WHERE
 (u0."id" = $1) AND
 (i3."tsv" @@ ts_rewrite(to_tsquery('english', $2), 'select t,s from aliases')) 
ORDER BY ts_rank_cd(i3."tsv", $3) DESC
LIMIT 28
[113011, "stoll:* | jf:* | 1978:* | pdf:*", "stoll:* | jf:* | 1978:* | pdf:*"]

returns

+------------------------------+
| name                         |
|------------------------------|
| Stoll_JF_1978.pdf            |
| HKO-value of info-JF82.pdf   |
| Glosten_JF_1994.pdf          |
| HJM_JF_2011.pdf              |
| BCW_JF_2000.pdf              |
| BGS_JFM_2005_overview.pdf    |
| Back_Baruch__JF_2007.pdf     |
| Glosten_Milgrom_1985_JFE.pdf |
+------------------------------+

and other columns in which there are no tsqueries …

Again, it works fine without ts_rank_cd here

|> order_by([u, us, s, f], ts_rank_cd(f.tsv, ^text))

Full stacktrace

** (exit) an exception was raised:
    ** (RuntimeError) type `tsquery` can not be handled by the types module Ecto.Adapters.Postgres.TypeModule
        (ecto) lib/ecto/adapters/postgres/connection.ex:79: Ecto.Adapters.Postgres.Connection.prepare_execute/5
        (ecto) lib/ecto/adapters/sql.ex:256: Ecto.Adapters.SQL.sql_call/6
        (ecto) lib/ecto/adapters/sql.ex:426: Ecto.Adapters.SQL.execute_and_cache/7
        (ecto) lib/ecto/repo/queryable.ex:133: Ecto.Repo.Queryable.execute/5
        (ecto) lib/ecto/repo/queryable.ex:37: Ecto.Repo.Queryable.all/4

Column tsv in f.tsv is a tsvector, but it’s not in ecto schema, so it’s not returned.

1 Like
2

order_by should have been

|> order_by([u, us, s, f], desc: ts_rank_cd(f.tsv, to_tsquery(^query)))

or something similar.

3 Likes