Comparing :utc_datetime and DateTime in Ecto 3 query

Learning Questions & Discussions Resources Chat/Questions
,
1

I’m getting this error:
## ** (Ecto.Query.CompileError) Tuples can only be used in comparisons with literal tuples of the same size

from this line in an Ecto query:

dynamic([mi], where: mi.updated_at >= ^datetime or ^conditions)

mi.updated_at is defined as: timestamps(type: :utc_datetime)

The incoming published_after is a unix epoch value which I convert to a DateTime with from_unix()

Pretty new to dates in Elixir in general, so I’m not sure what is going on. Any help?

Michael

    conditions =
      if published_after do
        {:ok, datetime} = DateTime.from_unix(published_after, :second)
        Logger.info("published_after: #{published_after}")
        Logger.info("datetime: #{datetime}")
        ## log output:
        ## [info] published_after: 1546312169
        ## [info] datetime: 2019-01-01 03:09:29Z
        ## ** (Ecto.Query.CompileError) Tuples can only be used in comparisons with literal tuples of the same size

        dynamic([mi], where: mi.updated_at >= ^datetime or ^conditions)
        # updated_at defined as:- timestamps(type: :utc_datetime)

      else
        conditions
      end
2

Try converting the UNIX seconds variable to Erlang tuple?

3

I tried this:

but same error. I think it has something to do with the fact that I use dynamic() because this chunk below compiles and runs(but returns inaccurate search results). I would like to use dynamic() because it makes it a lot easier to apply optional search parameters to a query.

compiles but returns incorrect results:

# compiles but does not return correct results
  def search(query_string, offset \\ 0, limit \\ 0,
    media_category,
    playlist_uuid,
    channel_uuid,
    published_after
      ) do

    {:ok, datetime} = DateTime.from_unix(published_after, :second)
    naive = DateTime.to_naive(datetime)

    query = Ecto.Query.from(mi in MediaItem,
      where: mi.updated_at >= ^naive
      )

    Logger.info("media_category: #{media_category}")
    Logger.info("naive: #{naive}")

    MediaItemsSearch.run(query, query_string)
      |> Repo.paginate(page: offset, page_size: limit)
  end

Compiles but runtime error:

#compiles but runtime error

#[debug] ** (Ecto.Query.CastError) apps/faithful_word_api/lib/faithful_word_api/controllers/api/v1.3/v13.ex:406: value `{{2019, 1, 1}, {3, 9, 29}}` in `where` cannot be cast to type :utc_datetime in query:

# from m0 in DB.Schema.MediaItem,
#   join: f1 in fragment("SELECT media_items_search.id AS id,\nts_rank(\n  media_items_search.document, plainto_tsquery(unaccent(?))\n) AS score\nFROM media_items_search\nWHERE media_items_search.document @@ plainto_tsquery(unaccent(?))\nOR media_items_search.localizedname ILIKE ?\n", ^"anderson", ^"anderson", ^"%anderson%"),
#   on: f1.id == m0.id,
#   where: m0.updated_at >= ^{{2019, 1, 1}, {3, 9, 29}},
#   select: count("*")


def search(query_string, offset \\ 0, limit \\ 0,
  media_category,
  playlist_uuid,
  channel_uuid,
  published_after
    ) do

  {:ok, datetime} = DateTime.from_unix(published_after, :second)
  naive = DateTime.to_naive(datetime)
  erl_datetime = NaiveDateTime.to_erl(datetime)

  query = Ecto.Query.from(mi in MediaItem,
    where: mi.updated_at >= ^erl_datetime
    )

  Logger.info("media_category: #{media_category}")
  Logger.info("naive: #{naive}")

  MediaItemsSearch.run(query, query_string)
    |> Repo.paginate(page: offset, page_size: limit)

# MediaItemsSearch.run(query, "thai chicken")
end
4

Ecto 3 does not allow erl_datetime anymore, since we have proper calendar types. You have to pass the naive_datetime or the datetime directly instead.

1 Like
5

Thanks, confirmed and working. I had a confluence of two bugs: using the wrong time format and incorrect syntax in the dynamic() macro.

For future readers(I ANDed the conditions, YMMV):

    conditions =
      if published_after do
        {:ok, datetime} = DateTime.from_unix(published_after, :second)
        naive = DateTime.to_naive(datetime)
        dynamic([mi], mi.updated_at >= ^naive and ^conditions)
      else
        conditions
      end

      query = Ecto.Query.from(mi in MediaItem, where: ^conditions)

    MediaItemsSearch.run(query, query_string)
      |> Repo.paginate(page: offset, page_size: limit)

More info:
https://hexdocs.pm/ecto/Ecto.Query.html#dynamic/2

1 Like