api.ex (23404B)
1 defmodule Ecto.Query.API do 2 @moduledoc """ 3 Lists all functions allowed in the query API. 4 5 * Comparison operators: `==`, `!=`, `<=`, `>=`, `<`, `>` 6 * Arithmetic operators: `+`, `-`, `*`, `/` 7 * Boolean operators: `and`, `or`, `not` 8 * Inclusion operator: `in/2` 9 * Subquery operators: `any`, `all` and `exists` 10 * Search functions: `like/2` and `ilike/2` 11 * Null check functions: `is_nil/1` 12 * Aggregates: `count/0`, `count/1`, `avg/1`, `sum/1`, `min/1`, `max/1` 13 * Date/time intervals: `datetime_add/3`, `date_add/3`, `from_now/2`, `ago/2` 14 * Inside select: `struct/2`, `map/2`, `merge/2`, `selected_as/2` and literals (map, tuples, lists, etc) 15 * General: `fragment/1`, `field/2`, `type/2`, `as/1`, `parent_as/1`, `selected_as/1` 16 17 Note the functions in this module exist for documentation 18 purposes and one should never need to invoke them directly. 19 Furthermore, it is possible to define your own macros and 20 use them in Ecto queries (see docs for `fragment/1`). 21 22 ## Intervals 23 24 Ecto supports following values for `interval` option: `"year"`, `"month"`, 25 `"week"`, `"day"`, `"hour"`, `"minute"`, `"second"`, `"millisecond"`, and 26 `"microsecond"`. 27 28 `Date`/`Time` functions like `datetime_add/3`, `date_add/3`, `from_now/2`, 29 `ago/2` take `interval` as an argument. 30 31 ## Window API 32 33 Ecto also supports many of the windows functions found 34 in SQL databases. See `Ecto.Query.WindowAPI` for more 35 information. 36 37 ## About the arithmetic operators 38 39 The Ecto implementation of these operators provide only 40 a thin layer above the adapters. So if your adapter allows you 41 to use them in a certain way (like adding a date and an 42 interval in PostgreSQL), it should work just fine in Ecto 43 queries. 44 """ 45 46 @dialyzer :no_return 47 48 @doc """ 49 Binary `==` operation. 50 """ 51 def left == right, do: doc! [left, right] 52 53 @doc """ 54 Binary `!=` operation. 55 """ 56 def left != right, do: doc! [left, right] 57 58 @doc """ 59 Binary `<=` operation. 60 """ 61 def left <= right, do: doc! [left, right] 62 63 @doc """ 64 Binary `>=` operation. 65 """ 66 def left >= right, do: doc! [left, right] 67 68 @doc """ 69 Binary `<` operation. 70 """ 71 def left < right, do: doc! [left, right] 72 73 @doc """ 74 Binary `>` operation. 75 """ 76 def left > right, do: doc! [left, right] 77 78 @doc """ 79 Binary `+` operation. 80 """ 81 def left + right, do: doc! [left, right] 82 83 @doc """ 84 Binary `-` operation. 85 """ 86 def left - right, do: doc! [left, right] 87 88 @doc """ 89 Binary `*` operation. 90 """ 91 def left * right, do: doc! [left, right] 92 93 @doc """ 94 Binary `/` operation. 95 """ 96 def left / right, do: doc! [left, right] 97 98 @doc """ 99 Binary `and` operation. 100 """ 101 def left and right, do: doc! [left, right] 102 103 @doc """ 104 Binary `or` operation. 105 """ 106 def left or right, do: doc! [left, right] 107 108 @doc """ 109 Unary `not` operation. 110 111 It is used to negate values in `:where`. It is also used to match 112 the assert the opposite of `in/2`, `is_nil/1`, and `exists/1`. 113 For example: 114 115 from p in Post, where: p.id not in [1, 2, 3] 116 117 from p in Post, where: not is_nil(p.title) 118 119 # Retrieve all the posts that doesn't have comments. 120 from p in Post, 121 as: :post, 122 where: 123 not exists( 124 from( 125 c in Comment, 126 where: parent_as(:post).id == c.post_id 127 ) 128 ) 129 130 """ 131 def not(value), do: doc! [value] 132 133 @doc """ 134 Checks if the left-value is included in the right one. 135 136 from p in Post, where: p.id in [1, 2, 3] 137 138 The right side may either be a list, a literal list 139 or even a column in the database with array type: 140 141 from p in Post, where: "elixir" in p.tags 142 143 Additionally, the right side may also be a subquery: 144 145 from c in Comment, where: c.post_id in subquery( 146 from(p in Post, where: p.created_at > ^since) 147 ) 148 """ 149 def left in right, do: doc! [left, right] 150 151 @doc """ 152 Evaluates to true if the provided subquery returns 1 or more rows. 153 154 from p in Post, 155 as: :post, 156 where: 157 exists( 158 from( 159 c in Comment, 160 where: parent_as(:post).id == c.post_id and c.replies_count > 5, 161 select: 1 162 ) 163 ) 164 165 This is best used in conjunction with `parent_as` to correlate the subquery 166 with the parent query to test some condition on related rows in a different table. 167 In the above example the query returns posts which have at least one comment that 168 has more than 5 replies. 169 """ 170 def exists(subquery), do: doc! [subquery] 171 172 @doc """ 173 Tests whether one or more values returned from the provided subquery match in a comparison operation. 174 175 from p in Product, where: p.id == any( 176 from(li in LineItem, select: [li.product_id], where: li.created_at > ^since and li.qty >= 10) 177 ) 178 179 A product matches in the above example if a line item was created since the provided date where the customer purchased 180 at least 10 units. 181 182 Both `any` and `all` must be given a subquery as an argument, and they must be used on the right hand side of a comparison. 183 Both can be used with every comparison operator: `==`, `!=`, `>`, `>=`, `<`, `<=`. 184 """ 185 def any(subquery), do: doc! [subquery] 186 187 @doc """ 188 Evaluates whether all values returned from the provided subquery match in a comparison operation. 189 190 from p in Post, where: p.visits >= all( 191 from(p in Post, select: avg(p.visits), group_by: [p.category_id]) 192 ) 193 194 For a post to match in the above example it must be visited at least as much as the average post in all categories. 195 196 from p in Post, where: p.visits == all( 197 from(p in Post, select: max(p.visits)) 198 ) 199 200 The above example matches all the posts which are tied for being the most visited. 201 202 Both `any` and `all` must be given a subquery as an argument, and they must be used on the right hand side of a comparison. 203 Both can be used with every comparison operator: `==`, `!=`, `>`, `>=`, `<`, `<=`. 204 """ 205 def all(subquery), do: doc! [subquery] 206 207 @doc """ 208 Searches for `search` in `string`. 209 210 from p in Post, where: like(p.body, "Chapter%") 211 212 Translates to the underlying SQL LIKE query, therefore 213 its behaviour is dependent on the database. In particular, 214 PostgreSQL will do a case-sensitive operation, while the 215 majority of other databases will be case-insensitive. For 216 performing a case-insensitive `like` in PostgreSQL, see `ilike/2`. 217 218 You should be very careful when allowing user sent data to be used 219 as part of LIKE query, since they allow to perform 220 [LIKE-injections](https://githubengineering.com/like-injection/). 221 """ 222 def like(string, search), do: doc! [string, search] 223 224 @doc """ 225 Searches for `search` in `string` in a case insensitive fashion. 226 227 from p in Post, where: ilike(p.body, "Chapter%") 228 229 Translates to the underlying SQL ILIKE query. This operation is 230 only available on PostgreSQL. 231 """ 232 def ilike(string, search), do: doc! [string, search] 233 234 @doc """ 235 Checks if the given value is nil. 236 237 from p in Post, where: is_nil(p.published_at) 238 239 To check if a given value is not nil use: 240 241 from p in Post, where: not is_nil(p.published_at) 242 """ 243 def is_nil(value), do: doc! [value] 244 245 @doc """ 246 Counts the entries in the table. 247 248 from p in Post, select: count() 249 """ 250 def count, do: doc! [] 251 252 @doc """ 253 Counts the given entry. 254 255 from p in Post, select: count(p.id) 256 """ 257 def count(value), do: doc! [value] 258 259 @doc """ 260 Counts the distinct values in given entry. 261 262 from p in Post, select: count(p.id, :distinct) 263 """ 264 def count(value, :distinct), do: doc! [value, :distinct] 265 266 @doc """ 267 Takes whichever value is not null, or null if they both are. 268 269 In SQL, COALESCE takes any number of arguments, but in ecto 270 it only takes two, so it must be chained to achieve the same 271 effect. 272 273 from p in Payment, select: p.value |> coalesce(p.backup_value) |> coalesce(0) 274 """ 275 def coalesce(value, expr), do: doc! [value, expr] 276 277 @doc """ 278 Applies the given expression as a FILTER clause against an 279 aggregate. This is currently only supported by Postgres. 280 281 from p in Payment, select: filter(avg(p.value), p.value > 0 and p.value < 100) 282 283 from p in Payment, select: avg(p.value) |> filter(p.value < 0) 284 """ 285 def filter(value, filter), do: doc! [value, filter] 286 287 @doc """ 288 Calculates the average for the given entry. 289 290 from p in Payment, select: avg(p.value) 291 """ 292 def avg(value), do: doc! [value] 293 294 @doc """ 295 Calculates the sum for the given entry. 296 297 from p in Payment, select: sum(p.value) 298 """ 299 def sum(value), do: doc! [value] 300 301 @doc """ 302 Calculates the minimum for the given entry. 303 304 from p in Payment, select: min(p.value) 305 """ 306 def min(value), do: doc! [value] 307 308 @doc """ 309 Calculates the maximum for the given entry. 310 311 from p in Payment, select: max(p.value) 312 """ 313 def max(value), do: doc! [value] 314 315 @doc """ 316 Adds a given interval to a datetime. 317 318 The first argument is a `datetime`, the second one is the count 319 for the interval, which may be either positive or negative and 320 the interval value: 321 322 # Get all items published since the last month 323 from p in Post, where: p.published_at > 324 datetime_add(^NaiveDateTime.utc_now(), -1, "month") 325 326 In the example above, we used `datetime_add/3` to subtract one month 327 from the current datetime and compared it with the `p.published_at`. 328 If you want to perform operations on date, `date_add/3` could be used. 329 330 See [Intervals](#module-intervals) for supported `interval` values. 331 """ 332 def datetime_add(datetime, count, interval), do: doc! [datetime, count, interval] 333 334 @doc """ 335 Adds a given interval to a date. 336 337 See `datetime_add/3` for more information. 338 339 See [Intervals](#module-intervals) for supported `interval` values. 340 """ 341 def date_add(date, count, interval), do: doc! [date, count, interval] 342 343 @doc """ 344 Adds the given interval to the current time in UTC. 345 346 The current time in UTC is retrieved from Elixir and 347 not from the database. 348 349 See [Intervals](#module-intervals) for supported `interval` values. 350 351 ## Examples 352 353 from a in Account, where: a.expires_at < from_now(3, "month") 354 355 """ 356 def from_now(count, interval), do: doc! [count, interval] 357 358 @doc """ 359 Subtracts the given interval from the current time in UTC. 360 361 The current time in UTC is retrieved from Elixir and 362 not from the database. 363 364 See [Intervals](#module-intervals) for supported `interval` values. 365 366 ## Examples 367 368 from p in Post, where: p.published_at > ago(3, "month") 369 """ 370 def ago(count, interval), do: doc! [count, interval] 371 372 @doc """ 373 Send fragments directly to the database. 374 375 It is not possible to represent all possible database queries using 376 Ecto's query syntax. When such is required, it is possible to use 377 fragments to send any expression to the database: 378 379 def unpublished_by_title(title) do 380 from p in Post, 381 where: is_nil(p.published_at) and 382 fragment("lower(?)", p.title) == ^title 383 end 384 385 Every occurrence of the `?` character will be interpreted as a place 386 for parameters, which must be given as additional arguments to 387 `fragment`. If the literal character `?` is required as part of the 388 fragment, it can be escaped with `\\\\?` (one escape for strings, 389 another for fragment). 390 391 In the example above, we are using the lower procedure in the 392 database to downcase the title column. 393 394 It is very important to keep in mind that Ecto is unable to do any 395 type casting when fragments are used. Therefore it may be necessary 396 to explicitly cast parameters via `type/2`: 397 398 fragment("lower(?)", p.title) == type(^title, :string) 399 400 ## Literals 401 402 Sometimes you need to interpolate a literal value into a fragment, 403 instead of a parameter. For example, you may need to pass a table 404 name or a collation, such as: 405 406 collation = "es_ES" 407 fragment("? COLLATE ?", ^name, ^collation) 408 409 The example above won't work because `collation` will be passed 410 as a parameter, while it has to be a literal part of the query. 411 412 You can address this by telling Ecto that variable is a literal: 413 414 fragment("? COLLATE ?", ^name, literal(^collation)) 415 416 Ecto will then escape it and make it part of the query. 417 418 > #### Literals and query caching {: .warning} 419 > 420 > Because literals are made part of the query, each interpolated 421 > literal will generate a separate query, with its own cache. 422 423 ## Defining custom functions using macros and fragment 424 425 You can add a custom Ecto query function using macros. For example 426 to expose SQL's coalesce function you can define this macro: 427 428 defmodule CustomFunctions do 429 defmacro coalesce(left, right) do 430 quote do 431 fragment("coalesce(?, ?)", unquote(left), unquote(right)) 432 end 433 end 434 end 435 436 To have coalesce/2 available, just import the module that defines it. 437 438 import CustomFunctions 439 440 The only downside is that it will show up as a fragment when 441 inspecting the Elixir query. Other than that, it should be 442 equivalent to a built-in Ecto query function. 443 444 ## Keyword fragments 445 446 In order to support databases that do not have string-based 447 queries, like MongoDB, fragments also allow keywords to be given: 448 449 from p in Post, 450 where: fragment(title: ["$eq": ^some_value]) 451 452 """ 453 def fragment(fragments), do: doc! [fragments] 454 455 @doc """ 456 Allows a field to be dynamically accessed. 457 458 def at_least_four(doors_or_tires) do 459 from c in Car, 460 where: field(c, ^doors_or_tires) >= 4 461 end 462 463 In the example above, both `at_least_four(:doors)` and `at_least_four(:tires)` 464 would be valid calls as the field is dynamically generated. 465 """ 466 def field(source, field), do: doc! [source, field] 467 468 @doc """ 469 Used in `select` to specify which struct fields should be returned. 470 471 For example, if you don't need all fields to be returned 472 as part of a struct, you can filter it to include only certain 473 fields by using `struct/2`: 474 475 from p in Post, 476 select: struct(p, [:title, :body]) 477 478 `struct/2` can also be used to dynamically select fields: 479 480 fields = [:title, :body] 481 from p in Post, select: struct(p, ^fields) 482 483 As a convenience, `select` allows developers to take fields 484 without an explicit call to `struct/2`: 485 486 from p in Post, select: [:title, :body] 487 488 Or even dynamically: 489 490 fields = [:title, :body] 491 from p in Post, select: ^fields 492 493 For preloads, the selected fields may be specified from the parent: 494 495 from(city in City, preload: :country, 496 select: struct(city, [:country_id, :name, country: [:id, :population]])) 497 498 If the same source is selected multiple times with a `struct`, 499 the fields are merged in order to avoid fetching multiple copies 500 from the database. In other words, the expression below: 501 502 from(city in City, preload: :country, 503 select: {struct(city, [:country_id]), struct(city, [:name])}) 504 505 is expanded to: 506 507 from(city in City, preload: :country, 508 select: {struct(city, [:country_id, :name]), struct(city, [:country_id, :name])}) 509 510 **IMPORTANT**: When filtering fields for associations, you 511 MUST include the foreign keys used in the relationship, 512 otherwise Ecto will be unable to find associated records. 513 """ 514 def struct(source, fields), do: doc! [source, fields] 515 516 @doc """ 517 Used in `select` to specify which fields should be returned as a map. 518 519 For example, if you don't need all fields to be returned or 520 neither need a struct, you can use `map/2` to achieve both: 521 522 from p in Post, 523 select: map(p, [:title, :body]) 524 525 `map/2` can also be used to dynamically select fields: 526 527 fields = [:title, :body] 528 from p in Post, select: map(p, ^fields) 529 530 If the same source is selected multiple times with a `map`, 531 the fields are merged in order to avoid fetching multiple copies 532 from the database. In other words, the expression below: 533 534 from(city in City, preload: :country, 535 select: {map(city, [:country_id]), map(city, [:name])}) 536 537 is expanded to: 538 539 from(city in City, preload: :country, 540 select: {map(city, [:country_id, :name]), map(city, [:country_id, :name])}) 541 542 For preloads, the selected fields may be specified from the parent: 543 544 from(city in City, preload: :country, 545 select: map(city, [:country_id, :name, country: [:id, :population]])) 546 547 It's also possible to select a struct from one source but only a subset of 548 fields from one of its associations: 549 550 from(city in City, preload: :country, 551 select: %{city | country: map(country: [:id, :population])}) 552 553 **IMPORTANT**: When filtering fields for associations, you 554 MUST include the foreign keys used in the relationship, 555 otherwise Ecto will be unable to find associated records. 556 """ 557 def map(source, fields), do: doc! [source, fields] 558 559 @doc """ 560 Merges the map on the right over the map on the left. 561 562 If the map on the left side is a struct, Ecto will check 563 all of the field on the right previously exist on the left 564 before merging. 565 566 from(city in City, select: merge(city, %{virtual_field: "some_value"})) 567 568 This function is primarily used by `Ecto.Query.select_merge/3` 569 to merge different select clauses. 570 """ 571 def merge(left_map, right_map), do: doc! [left_map, right_map] 572 573 @doc """ 574 Returns value from the `json_field` pointed to by `path`. 575 576 from(post in Post, select: json_extract_path(post.meta, ["author", "name"])) 577 578 The query can be also rewritten as: 579 580 from(post in Post, select: post.meta["author"]["name"]) 581 582 Path elements can be integers to access values in JSON arrays: 583 584 from(post in Post, select: post.meta["tags"][0]["name"]) 585 586 Any element of the path can be dynamic: 587 588 field = "name" 589 from(post in Post, select: post.meta["author"][^field]) 590 591 ## Warning: indexes on PostgreSQL 592 593 PostgreSQL supports indexing on jsonb columns via GIN indexes. 594 Whenever comparing the value of a jsonb field against a string 595 or integer, Ecto will use the containement operator @> which 596 is optimized. You can even use the more efficient `jsonb_path_ops` 597 GIN index variant. For more information, consult PostgreSQL's docs 598 on [JSON indexing](https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING). 599 600 ## Warning: return types 601 602 The underlying data in the JSON column is returned without any 603 additional decoding. This means "null" JSON values are not the 604 same as SQL's "null". For example, the `Repo.all` operation below 605 returns an empty list because `p.meta["author"]` returns JSON's 606 null and therefore `is_nil` does not succeed: 607 608 Repo.insert!(%Post{meta: %{author: nil}}) 609 Repo.all(from(post in Post, where: is_nil(p.meta["author"]))) 610 611 Similarly, other types, such as datetimes, are returned as strings. 612 This means conditions like `post.meta["published_at"] > from_now(-1, "day")` 613 may return incorrect results or fail as the underlying database 614 tries to compare incompatible types. You can, however, use `type/2` 615 to force the types on the database level. 616 """ 617 def json_extract_path(json_field, path), do: doc! [json_field, path] 618 619 @doc """ 620 Casts the given value to the given type at the database level. 621 622 Most of the times, Ecto is able to proper cast interpolated 623 values due to its type checking mechanism. In some situations 624 though, you may want to tell Ecto that a parameter has some 625 particular type: 626 627 type(^title, :string) 628 629 It is also possible to say the type must match the same of a column: 630 631 type(^title, p.title) 632 633 Or a parameterized type, which must be previously initialized 634 with `Ecto.ParameterizedType.init/2`: 635 636 @my_enum Ecto.ParameterizedType.init(Ecto.Enum, values: [:foo, :bar, :baz]) 637 type(^title, ^@my_enum) 638 639 Ecto will ensure `^title` is cast to the given type and enforce such 640 type at the database level. If the value is returned in a `select`, 641 Ecto will also enforce the proper type throughout. 642 643 When performing arithmetic operations, `type/2` can be used to cast 644 all the parameters in the operation to the same type: 645 646 from p in Post, 647 select: type(p.visits + ^a_float + ^a_integer, :decimal) 648 649 Inside `select`, `type/2` can also be used to cast fragments: 650 651 type(fragment("NOW"), :naive_datetime) 652 653 Or to type fields from schemaless queries: 654 655 from p in "posts", select: type(p.cost, :decimal) 656 657 Or to type aggregation results: 658 659 from p in Post, select: type(avg(p.cost), :integer) 660 from p in Post, select: type(filter(avg(p.cost), p.cost > 0), :integer) 661 662 Or to type comparison expression results: 663 664 from p in Post, select: type(coalesce(p.cost, 0), :integer) 665 666 Or to type fields from a parent query using `parent_as/1`: 667 668 child = from c in Comment, where: type(parent_as(:posts).id, :string) == c.text 669 from Post, as: :posts, inner_lateral_join: c in subquery(child), select: c.text 670 671 """ 672 def type(interpolated_value, type), do: doc! [interpolated_value, type] 673 674 @doc """ 675 Refer to a named atom binding. 676 677 See the "Named binding" section in `Ecto.Query` for more information. 678 """ 679 def as(binding), do: doc! [binding] 680 681 @doc """ 682 Refer to a named atom binding in the parent query. 683 684 This is available only inside subqueries. 685 686 See the "Named binding" section in `Ecto.Query` for more information. 687 """ 688 def parent_as(binding), do: doc! [binding] 689 690 @doc """ 691 Refer to an alias of a selected value. 692 693 This can be used to refer to aliases created using `selected_as/2`. If 694 the alias hasn't been created using `selected_as/2`, an error will be raised. 695 696 Each database has its own rules governing which clauses can reference these aliases. 697 If an error is raised mentioning an unknown column, most likely the alias is being 698 referenced somewhere that is not allowed. Consult the documentation for the database 699 to ensure the alias is being referenced correctly. 700 """ 701 def selected_as(name), do: doc! [name] 702 703 @doc """ 704 Creates an alias for the given selected value. 705 706 When working with calculated values, an alias can be used to simplify 707 the query. Otherwise, the entire expression would need to be copied when 708 referencing it outside of select statements. 709 710 This comes in handy when, for instance, you would like to use the calculated 711 value in `Ecto.Query.group_by/3` or `Ecto.Query.order_by/3`: 712 713 from p in Post, 714 select: %{ 715 posted: selected_as(p.posted, :date), 716 sum_visits: p.visits |> coalesce(0) |> sum() |> selected_as(:sum_visits) 717 }, 718 group_by: selected_as(:date), 719 order_by: selected_as(:sum_visits) 720 721 The name of the alias must be an atom and it can only be used in the outer most 722 select expression, otherwise an error is raised. Please note that the alias name 723 does not have to match the key when `select` returns a map, struct or keyword list. 724 725 Using this in conjunction with `selected_as/1` is recommended to ensure only defined aliases 726 are referenced. 727 """ 728 def selected_as(selected_value, name), do: doc! [selected_value, name] 729 730 defp doc!(_) do 731 raise "the functions in Ecto.Query.API should not be invoked directly, " <> 732 "they serve for documentation purposes only" 733 end 734 end