The current time in UTC is retrieved from Elixir and not from the database. from_now(count, interval)Īdds the given interval to the current time in UTC. Other than that, it should be equivalent to a built-in Ecto query function. The only downside is that it will show up as a fragment when inspecting the Elixir query. To have coalesce/2 available, just import the module that defines it. For example to expose SQL’s coalesce function you can define this macro: defmodule CustomFunctions doįragment("coalesce(?, ?)", unquote(left), unquote(right)) You can add a custom Ecto query function using macros. Where: fragment(title: ) Defining custom functions using macros and fragment In order to support databases that do not have string-based queries, like MongoDB, fragments also allow keywords to be given: from p in Post, It is possible to make use of PostgreSQL’s JSON/JSONB data type with fragments, as well: fragment("?->? ILIKE ?", p.map, "key_name", ^some_value) Keyword fragments Or even say the right side is of the same type as p.title: fragment("lower(?)", p.title) = type(^title, p.title) You can however use the type/2 function to give Ecto some hints: fragment("lower(?)", p.title) = type(^title, :string) It is very important to keep in mind that Ecto is unable to do any type casting described above when fragments are used. In the example above, we are using the lower procedure in the database to downcase the title column. When such is required, it is possible to use fragments to send any expression to the database: def unpublished_by_title(title) do It is not possible to represent all possible database queries using Ecto’s query syntax. In the example above, both at_least_four(:doors) and at_least_four(:tires) would be valid calls as the field is dynamically generated. field(source, field)Īllows a field to be dynamically accessed. The following intervals are supported: year, month, week, day, hour, minute, second, millisecond and microsecond. If you want to perform operations on date, date_add/3 could be used. In the example above, we used datetime_add/3 to subtract one month from the current datetime and compared it with the p.published_at. The first argument is a datetime, the second one is the count for the interval, which may be either positive or negative and the interval value: # Get all items published since the last monthĭatetime_add(^, -1, "month") from p in Post, select: count(p.id, :distinct) date_add(date, count, interval) from p in Post, select: count(p.id) count(value, atom)Ĭounts the distinct values in given entry. from p in Payment, select: avg(p.value) count(value)Ĭounts the given entry. Examples from p in Post, where: p.published_at > ago(3, "month") left and rightĬalculates the average for the given entry. Substracts the given interval from the current time in UTC. Used in select to specify which struct fields should be returned sum(value)Ĭalculates the sum for the given entry type(interpolated_value, type)Ĭasts the given value to the given type Functions left != rightīinary >= operation. Used in select to specify which fields should be returned as a map max(value)Ĭalculates the maximum for the given entry min(value)Ĭalculates the minimum for the given entry not valueīinary or operation struct(source, fields) Searches for search in string map(source, fields) Searches for search in string in a case insensitive fashion left in rightĬhecks if the left-value is included in the right one is_nil(value)Ĭhecks if the given value is nil like(string, search) Send fragments directly to the database from_now(count, interval)Īdds the given interval to the current time in UTC ilike(string, search) Substracts the given interval from the current time in UTC left and rightĬalculates the average for the given entry count(value)Ĭounts the given entry count(value, atom)Ĭounts the distinct values in given entry date_add(date, count, interval)Īdds a given interval to a date datetime_add(datetime, count, interval)Īdds a given interval to a datetime field(source, field)Īllows a field to be dynamically accessed fragment(fragments) Furthermore, it is possible to define your own macros and use them in Ecto queries (see docs for fragment/1). Note the functions in this module exist for documentation purposes and one should never need to invoke them directly.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |