/
query.ex
1342 lines (980 loc) · 43.1 KB
/
query.ex
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
defmodule Ecto.SubQuery do
@moduledoc """
Stores subquery information.
"""
defstruct [:query, :params, :select, :cache]
end
defmodule Ecto.Query do
@moduledoc ~S"""
Provides the Query DSL.
Queries are used to retrieve and manipulate data from a repository
(see `Ecto.Repo`). Ecto queries come in two flavors: keyword-based
and macro-based. Most examples will use the keyword-based syntax,
the macro one will be explored in later sections.
Let's see a sample query:
# Imports only from/2 of Ecto.Query
import Ecto.Query, only: [from: 2]
# Create a query
query = from u in "users",
where: u.age > 18,
select: u.name
# Send the query to the repository
Repo.all(query)
In the example above, we are directly querying the "users" table
from the database.
## Query expressions
Ecto allows a limited set of expressions inside queries. In the
query below, for example, we use `u.age` to access a field, the
`>` comparison operator and the literal `0`:
query = from u in "users", where: u.age > 0, select: u.name
You can find the full list of operations in `Ecto.Query.API`.
Besides the operations listed there, the following literals are
supported in queries:
* Integers: `1`, `2`, `3`
* Floats: `1.0`, `2.0`, `3.0`
* Booleans: `true`, `false`
* Binaries: `<<1, 2, 3>>`
* Strings: `"foo bar"`, `~s(this is a string)`
* Arrays: `[1, 2, 3]`, `~w(interpolate words)`
All other types and dynamic values must be passed as a parameter using
interpolation as explained below.
## Interpolation and casting
External values and Elixir expressions can be injected into a query
expression with `^`:
def with_minimum(age, height_ft) do
from u in "users",
where: u.age > ^age and u.height > ^(height_ft * 3.28),
select: u.name
end
with_minimum(18, 5.0)
When interpolating values, you may want to explicitly tell Ecto
what is the expected type of the value being interpolated:
age = "18"
Repo.all(from u in "users",
where: u.age > type(^age, :integer),
select: u.name)
In the example above, Ecto will cast the age to type integer. When
a value cannot be cast, `Ecto.Query.CastError` is raised.
To avoid the repetition of always specifying the types, you may define
an `Ecto.Schema`. In such cases, Ecto will analyze your queries and
automatically cast the interpolated "age" when compared to the `u.age`
field, as long as the age field is defined with type `:integer` in
your schema:
age = "18"
Repo.all(from u in User, where: u.age > ^age, select: u.name)
Another advantage of using schemas is that we no longer need to specify
the select option in queries, as by default Ecto will retrieve all
fields specified in the schema:
age = "18"
Repo.all(from u in User, where: u.age > ^age)
For this reason, we will use schemas on the remaining examples but
remember Ecto does not require them in order to write queries.
## Composition
Ecto queries are composable. For example, the query above can
actually be defined in two parts:
# Create a query
query = from u in User, where: u.age > 18
# Extend the query
query = from u in query, select: u.name
Composing queries uses the same syntax as creating a query.
The difference is that, instead of passing a schema like `User`
on the right side of `in`, we passed the query itself.
Any value can be used on the right-side of `in` as long as it implements
the `Ecto.Queryable` protocol. For now, we know the protocol is
implemented for both atoms (like `User`) and strings (like "users").
In any case, regardless if a schema has been given or not, Ecto
queries are always composable thanks to its binding system.
### Query bindings
On the left side of `in` we specify the query bindings. This is
done inside from and join clauses. In the query below `u` is a
binding and `u.age` is a field access using this binding.
query = from u in User, where: u.age > 18
Bindings are not exposed from the query. When composing queries you
must specify bindings again for each refinement query. For example
to further narrow-down above query we again need to tell Ecto what
bindings to expect:
query = from u in query, select: u.city
Bindings in Ecto are positional, and the names do not have to be
consistent between input and refinement queries. For example, the
query above could also be written as:
query = from q in query, select: q.city
It would make no difference to Ecto. This is important because
it allows developers to compose queries without caring about
the bindings used in the initial query.
When using joins, the bindings should be matched in the order they
are specified:
# Create a query
query = from p in Post,
join: c in Comment, where: c.post_id == p.id
# Extend the query
query = from [p, c] in query,
select: {p.title, c.body}
You are not required to specify all bindings when composing.
For example, if we would like to order the results above by
post insertion date, we could further extend it as:
query = from q in query, order_by: q.inserted_at
The example above will work if the input query has 1 or 10
bindings. In the example above, we will always sort by the
`inserted_at` column from the `from` source.
Similarly, if you are interested only on the last binding
(or the last bindings) in a query, you can use ... to
specify "all bindings before" and match on the last one.
For instance, imagine you wrote:
posts_with_comments =
from p in query, join: c in Comment, where: c.post_id == p.id
And now we want to make sure to return both the post title
and the comment body. Although we may not know how many
bindings there are in the query, we are sure posts is the
first binding and comments are the last one, so we can write:
from [p, ..., c] in posts_with_comments, select: {p.title, c.body}
In other words, `...` will include all the binding between the first and
the last, which may be no binding at all, one or many. Using `...` can
be handy from time to time but most of its uses can be avoided by relying
on the keyword query syntax when writing queries.
### Bindingless operations
Although bindings are extremely useful when working with joins,
they are not necessary when the query has only the `from` clause.
For such cases, Ecto supports a way for building queries
without specifying the binding:
from Post,
where: [category: "fresh and new"],
order_by: [desc: :published_at],
select: [:id, :title, :body]
The query above will select all posts with category "fresh and new",
order by the most recently published, and return Post structs with
only the id, title and body fields set. It is equivalent to:
from p in Post,
where: p.category == "fresh and new",
order_by: [desc: p.published_at],
select: struct(p, [:id, :title, :body])
One advantage of bindingless queries is that they are data-driven
and therefore useful for dynamically building queries. For example,
the query above could also be written as:
where = [category: "fresh and new"]
order_by = [desc: :published_at]
select = [:id, :title, :body]
from Post, where: ^where, order_by: ^order_by, select: ^select
This feature is very useful when queries need to be built based
on some user input, like web search forms, CLIs and so on.
## Fragments
If you need an escape hatch, Ecto provides fragments
(see `Ecto.Query.API.fragment/1`) to inject SQL (and non-SQL)
fragments into queries.
For example, to get all posts while running the "lower(?)"
function in the database where `p.title` is interpolated
in place of `?`, one can write:
from p in Post,
where: is_nil(p.published_at) and
fragment("lower(?)", p.title) == ^title
Also, most adapters provide direct APIs for queries, like
`Ecto.Adapters.SQL.query/4`, allowing developers to
completely bypass Ecto queries.
## Macro API
In all examples so far we have used the **keywords query syntax** to
create a query:
import Ecto.Query
from u in "users", where: u.age > 18, select: u.name
Due to the prevalence of the pipe operator in Elixir, Ecto also supports
a pipe-based syntax:
"users"
|> where([u], u.age > 18)
|> select([u], u.name)
The keyword-based and pipe-based examples are equivalent. The downside
of using macros is that the binding must be specified for every operation.
However, since keyword-based and pipe-based examples are equivalent, the
bindingless syntax also works for macros:
"users"
|> where([u], u.age > 18)
|> select([:name])
Such allows developers to write queries using bindings only in more
complex query expressions.
This module documents each of those macros, providing examples in
both the keywords query and pipe expression formats.
## Query Prefix
It is possible to set a prefix for the queries. For Postgres users,
this will specify the schema where the table is located, while for
MySQL users this will specify the database where the table is
located. When no prefix is set, Postgres queries are assumed to be
in the public schema, while MySQL queries are assumed to be in the
database set in the config for the repo.
To set the prefix on a query:
results =
query # May be User or an Ecto.Query itself
|> Ecto.Queryable.to_query
|> Map.put(:prefix, "foo")
|> Repo.all
When a prefix is set in a query, all loaded structs will belong to that
prefix, so operations like update and delete will be applied to the
proper prefix. In case you want to manually set the prefix for new data,
specially on insert, use `Ecto.put_meta/2`.
"""
defstruct [prefix: nil, sources: nil, from: nil, joins: [], wheres: [], select: nil,
order_bys: [], limit: nil, offset: nil, group_bys: [], updates: [],
havings: [], preloads: [], assocs: [], distinct: nil, lock: nil]
@type t :: %__MODULE__{}
defmodule DynamicExpr do
@moduledoc false
defstruct [:fun, :binding, :file, :line]
end
defmodule QueryExpr do
@moduledoc false
defstruct [:expr, :file, :line, params: []]
end
defmodule BooleanExpr do
@moduledoc false
defstruct [:op, :expr, :file, :line, params: []]
end
defmodule SelectExpr do
@moduledoc false
defstruct [:expr, :file, :line, :fields, params: [], take: %{}]
end
defmodule JoinExpr do
@moduledoc false
defstruct [:qual, :source, :on, :file, :line, :assoc, :ix, params: []]
end
defmodule Tagged do
@moduledoc false
# * value is the tagged value
# * tag is the directly tagged value, like Ecto.DateTime
# * type is the underlying tag type, like :datetime
defstruct [:value, :tag, :type]
end
alias Ecto.Query.Builder
alias Ecto.Query.Builder.{Distinct, Dynamic, Filter, From, GroupBy, Join,
LimitOffset, Lock, OrderBy, Preload, Select, Update}
@doc """
Builds a dynamic query expression.
Dynamic query expressions allows developers to build queries
expression bit by bit so they are later interpolated in a query.
## Examples
For example, imagine you have a set of conditions you want to
build your query on:
dynamic = false
dynamic =
if params["is_public"] do
dynamic([p], p.is_public or ^dynamic)
else
dynamic
end
dynamic =
if params["allow_reviewers"] do
dynamic([p, a], a.reviewer == true or ^dynamic)
else
dynamic
end
from query, where: ^dynamic
In the example above, we were able to build the query expressions
bit by bit, using different bindings, and later interpolate it all
at once inside the query.
A dynamic expression can always be interpolated inside another dynamic
expression and into the constructs described below.
## `where`, `having` and a `join`'s `on'
`dynamic` can be interpolated at the root of a `where`, `having` or
a `join`'s `on`.
For example, the following is forbidden because it is not at the
root of a `where`:
from q in query, where: q.some_condition and ^dynamic
Fortunately that's easily solvable by simply rewriting it to:
dynamic = dynamic([q], q.some_condition and ^dynamic)
from query, where: ^dynamic
## Updates
Dynamic is also supported as each field in an update, for example:
update_to = dynamic([p], p.sum / p.count)
from query, update: [set: [average: ^update_to]]
"""
defmacro dynamic(binding \\ [], expr) do
Dynamic.build(binding, expr, __CALLER__)
end
@doc """
Converts a query into a subquery.
If a subquery is given, returns the subquery itself.
If any other value is given, it is converted to a query via
`Ecto.Queryable` and wrapped in the `Ecto.SubQuery` struct.
Subqueries are currently only supported in the `from`
and `join` fields.
## Examples
# Get the average salary of the top 10 highest salaries
query = from Employee, order_by: [desc: :salary], limit: 10
from e in subquery(query), select: avg(e.salary)
Although subqueries are not allowed in WHERE expressions,
most subqueries in WHERE expression can be rewritten as JOINs.
Imagine you want to write this query:
UPDATE posts
SET sync_started_at = $1
WHERE id IN (
SELECT id FROM posts
WHERE synced = false AND (sync_started_at IS NULL OR sync_started_at < $1)
LIMIT $2
)
If you attempt to write it as `where: p.id in ^subquery(foo)`,
Ecto won't accept such query. However, the subquery above can be
written as a JOIN, which is supported by Ecto. The final Ecto
query will look like this:
subset_query = from(p in Post,
where: p.synced == false and
(is_nil(p.sync_started_at) or p.sync_started_at < ^min_sync_started_at),
limit: ^batch_size
)
Repo.update_all(
from(p in Post, join: s in subquery(subset_query), on: s.id == p.id),
set: [sync_started_at: NaiveDateTime.utc_now()]
end)
"""
def subquery(%Ecto.SubQuery{} = subquery),
do: subquery
def subquery(%Ecto.Query{} = query),
do: %Ecto.SubQuery{query: query}
def subquery(queryable),
do: %Ecto.SubQuery{query: Ecto.Queryable.to_query(queryable)}
@doc """
Resets a previously set field on a query.
It can reset any query field except the query source (`from`).
## Example
query |> Ecto.Query.exclude(:select)
"""
def exclude(%Ecto.Query{} = query, field), do: do_exclude(query, field)
def exclude(query, field), do: do_exclude(Ecto.Queryable.to_query(query), field)
defp do_exclude(%Ecto.Query{} = query, :join), do: %{query | joins: []}
defp do_exclude(%Ecto.Query{} = query, :where), do: %{query | wheres: []}
defp do_exclude(%Ecto.Query{} = query, :order_by), do: %{query | order_bys: []}
defp do_exclude(%Ecto.Query{} = query, :group_by), do: %{query | group_bys: []}
defp do_exclude(%Ecto.Query{} = query, :having), do: %{query | havings: []}
defp do_exclude(%Ecto.Query{} = query, :distinct), do: %{query | distinct: nil}
defp do_exclude(%Ecto.Query{} = query, :select), do: %{query | select: nil}
defp do_exclude(%Ecto.Query{} = query, :limit), do: %{query | limit: nil}
defp do_exclude(%Ecto.Query{} = query, :offset), do: %{query | offset: nil}
defp do_exclude(%Ecto.Query{} = query, :lock), do: %{query | lock: nil}
defp do_exclude(%Ecto.Query{} = query, :preload), do: %{query | preloads: [], assocs: []}
@doc """
Creates a query.
It can either be a keyword query or a query expression.
If it is a keyword query the first argument must be
either an `in` expression, or a value that implements
the `Ecto.Queryable` protocol. If the query needs a
reference to the data source in any other part of the
expression, then an `in` must be used to create a reference
variable. The second argument should be a keyword query
where the keys are expression types and the values are
expressions.
If it is a query expression the first argument must be
a value that implements the `Ecto.Queryable` protocol
and the second argument the expression.
## Keywords example
from(c in City, select: c)
## Expressions example
City |> select([c], c)
## Examples
def paginate(query, page, size) do
from query,
limit: ^size,
offset: ^((page-1) * size)
end
The example above does not use `in` because `limit` and `offset`
do not require a reference to the data source. However, extending
the query with a where expression would require the use of `in`:
def published(query) do
from p in query, where: not(is_nil(p.published_at))
end
Notice we have created a `p` variable to reference the query's
original data source. This assumes that the original query
only had one source. When the given query has more than one source, a variable
must be given for each in the order they were bound:
def published_multi(query) do
from [p,o] in query,
where: not(is_nil(p.published_at)) and not(is_nil(o.published_at))
end
Note the variables `p` and `o` can be named whatever you like
as they have no importance in the query sent to the database.
"""
defmacro from(expr, kw \\ []) do
unless Keyword.keyword?(kw) do
raise ArgumentError, "second argument to `from` must be a compile time keyword list"
end
{quoted, binds, count_bind} = From.build(expr, __CALLER__)
from(kw, __CALLER__, count_bind, quoted, binds)
end
@binds [:where, :or_where, :select, :distinct, :order_by, :group_by,
:having, :or_having, :limit, :offset, :preload, :update, :select_merge]
@no_binds [:lock]
@joins [:join, :inner_join, :cross_join, :left_join, :right_join, :full_join,
:inner_lateral_join, :left_lateral_join]
defp from([{type, expr}|t], env, count_bind, quoted, binds) when type in @binds do
# If all bindings are integer indexes keep AST Macro expandable to %Query{},
# otherwise ensure that quoted code is evaluated before macro call
quoted =
if Enum.all?(binds, fn {_, value} -> is_integer(value) end) do
quote do
Ecto.Query.unquote(type)(unquote(quoted), unquote(binds), unquote(expr))
end
else
quote do
query = unquote(quoted)
Ecto.Query.unquote(type)(query, unquote(binds), unquote(expr))
end
end
from(t, env, count_bind, quoted, binds)
end
defp from([{type, expr}|t], env, count_bind, quoted, binds) when type in @no_binds do
quoted =
quote do
Ecto.Query.unquote(type)(unquote(quoted), unquote(expr))
end
from(t, env, count_bind, quoted, binds)
end
defp from([{join, expr}|t], env, count_bind, quoted, binds) when join in @joins do
qual =
case join do
:join -> :inner
:full_join -> :full
:left_join -> :left
:right_join -> :right
:inner_join -> :inner
:cross_join -> :cross
:left_lateral_join -> :left_lateral
:inner_lateral_join -> :inner_lateral
end
{t, on} = collect_on(t, nil)
{quoted, binds, count_bind} = Join.build(quoted, qual, binds, expr, on, count_bind, env)
from(t, env, count_bind, quoted, binds)
end
defp from([{:on, _value}|_], _env, _count_bind, _quoted, _binds) do
Builder.error! "`on` keyword must immediately follow a join"
end
defp from([{key, _value}|_], _env, _count_bind, _quoted, _binds) do
Builder.error! "unsupported #{inspect key} in keyword query expression"
end
defp from([], _env, _count_bind, quoted, _binds) do
quoted
end
defp collect_on([{:on, expr}|t], nil),
do: collect_on(t, expr)
defp collect_on([{:on, expr}|t], acc),
do: collect_on(t, {:and, [], [acc, expr]})
defp collect_on(other, acc),
do: {other, acc}
@doc """
A join query expression.
Receives a source that is to be joined to the query and a condition for
the join. The join condition can be any expression that evaluates
to a boolean value. The join is by default an inner join, the qualifier
can be changed by giving the atoms: `:inner`, `:left`, `:right`, `:cross`,
`:full`, `:inner_lateral` or `:left_lateral`. For a keyword query the `:join`
keyword can be changed to: `:inner_join`, `:left_join`, `:right_join`,
`:cross_join`, `:full_join`, `:inner_lateral_join` or `:left_lateral_join`.
Currently it is possible to join on:
* an `Ecto.Schema`, such as `p in Post`
* an Ecto query with zero or more where clauses, such as `from "posts", where: [public: true]`
* an association, such as `c in assoc(post, :comments)`
* a query fragment, such as `c in fragment("SOME COMPLEX QUERY")`
* a subquery, such as `c in subquery(another_query)`
The fragment support exists mostly for handling lateral joins.
See "Joining with fragments" below.
## Keywords examples
from c in Comment,
join: p in Post, on: p.id == c.post_id,
select: {p.title, c.text}
from p in Post,
left_join: c in assoc(p, :comments),
select: {p, c}
Keywords can also be given or interpolated as part of `on`:
from c in Comment,
join: p in Post, on: [id: c.post_id],
select: {p.title, c.text}
Any key in `on` will apply to the currently joined expression.
It is also possible to interpolate an Ecto query on the right side
of `in`. For example, the query above can also be written as:
posts = Post
from c in Comment,
join: p in ^posts, on: [id: c.post_id],
select: {p.title, c.text}
The above is specially useful to dynamically join on existing
queries, for example, choosing between public posts or posts
that have been recently published:
posts =
if params["drafts"] do
from p in Post, where: [drafts: true]
else
from p in Post, where: [public: true]
end
from c in Comment,
join: p in ^posts, on: [id: c.post_id],
select: {p.title, c.text}
Only simple queries with `where` expressions can be interpolated
in join.
## Expressions examples
Comment
|> join(:inner, [c], p in Post, c.post_id == p.id)
|> select([c, p], {p.title, c.text})
Post
|> join(:left, [p], c in assoc(p, :comments))
|> select([p, c], {p, c})
Post
|> join(:left, [p], c in Comment, c.post_id == p.id and c.is_visible == true)
|> select([p, c], {p, c})
## Joining with fragments
When you need to join on a complex query, Ecto supports fragments in joins:
Comment
|> join(:inner, [c], p in fragment("SOME COMPLEX QUERY", c.id, ^some_param))
Although using fragments in joins is discouraged in favor of Ecto
Query syntax, they are necessary when writing lateral joins as
lateral joins require a subquery that refer to previous bindings:
Game
|> join(:inner_lateral, [g], gs in fragment("SELECT * FROM games_sold AS gs WHERE gs.game_id = ? ORDER BY gs.sold_on LIMIT 2", g.id))
|> select([g, gs], {g.name, gs.sold_on})
"""
defmacro join(query, qual, binding \\ [], expr, on \\ nil) do
Join.build(query, qual, binding, expr, on, nil, __CALLER__)
|> elem(0)
end
@doc """
A select query expression.
Selects which fields will be selected from the schema and any transformations
that should be performed on the fields. Any expression that is accepted in a
query can be a select field.
Selelct also allows each expression to be wrapped in lists, tuples or maps as
shown in the examples below. A full schema can also be selected.
There can only be one select expression in a query, if the select expression
is omitted, the query will by default select the full schema. If select is
given more than once, an error is raised. Use `exclude/2` if you would like
to remove a previous select for overriding or see `select_merge/3` for a
limited version of `select` that is composable and can be called multiple
times.
`select` also accepts a list of atoms where each atom refers to a field in
the source to be selected.
## Keywords examples
from(c in City, select: c) # returns the schema as a struct
from(c in City, select: {c.name, c.population})
from(c in City, select: [c.name, c.county])
from(c in City, select: %{n: c.name, answer: 42})
from(c in City, select: %{c | alternative_name: c.name})
from(c in City, select: %Data{name: c.name})
It is also possible to select a struct and limit the returned
fields at the same time:
from(City, select: [:name])
The syntax above is equivalent to:
from(city in City, select: struct(city, [:name]))
You can also write:
from(city in City, select: map(city, [:name]))
If you want a map with only the selected fields to be returned.
For more information, read the docs for `Ecto.Query.API.struct/2`
and `Ecto.Query.API.map/2`.
## Expressions examples
City |> select([c], c)
City |> select([c], {c.name, c.country})
City |> select([c], %{"name" => c.name})
City |> select([:name])
City |> select([c], struct(c, [:name]))
City |> select([c], map(c, [:name]))
"""
defmacro select(query, binding \\ [], expr) do
Select.build(:select, query, binding, expr, __CALLER__)
end
@doc """
Mergeable select query expression.
This macro is similar to `select/3` except it may be specified
multiple times as long as every entry is a map. This is useful
for merging and composing selects. For example:
query = from p in Post, select: %{}
query =
if include_title? do
from p in query, select_merge: %{title: p.title}
else
query
end
query =
if include_visits? do
from p in query, select_merge: %{visits: p.visits}
else
query
end
In the example above, the query is built little by little by merging
into a final map. If both conditions above are true, the final query
would be equivalent to:
from p in Post, select: %{title: p.title, visits: p.visits}
If `:select_merge` is called and there is no value selected previously,
it will default to the source, `p` in the example above.
The left-side of a merge can be a struct or a map. The right side
must always be a map. If the left-side is a struct, the fields on
the right side must be part of the struct, otherwise an error is
raised.
"""
defmacro select_merge(query, binding \\ [], expr) do
Select.build(:merge, query, binding, expr, __CALLER__)
end
@doc """
A distinct query expression.
When true, only keeps distinct values from the resulting
select expression.
If supported by your database, you can also pass query expressions
to distinct and it will generate a query with DISTINCT ON. In such
cases, `distinct` accepts exactly the same expressions as `order_by`
and any `distinct` expression will be automatically prepended to the
`order_by` expressions in case there is any `order_by` expression.
## Keywords examples
# Returns the list of different categories in the Post schema
from(p in Post, distinct: true, select: p.category)
# If your database supports DISTINCT ON(),
# you can pass expressions to distinct too
from(p in Post,
distinct: p.category,
order_by: [p.date])
# The DISTINCT ON() also supports ordering similar to ORDER BY.
from(p in Post,
distinct: [desc: p.category],
order_by: [p.date])
# Using atoms
from(p in Post, distinct: :category, order_by: :date)
## Expressions example
Post
|> distinct(true)
|> order_by([p], [p.category, p.author])
"""
defmacro distinct(query, binding \\ [], expr) do
Distinct.build(query, binding, expr, __CALLER__)
end
@doc """
An AND where query expression.
`where` expressions are used to filter the result set. If there is more
than one where expression, they are combined with an `and` operator. All
where expressions have to evaluate to a boolean value.
`where` also accepts a keyword list where the field given as key is going to
be compared with the given value. The fields will always refer to the source
given in `from`.
## Keywords example
from(c in City, where: c.country == "Sweden")
from(c in City, where: [country: "Sweden"])
It is also possible to interpolate the whole keyword list, allowing you to
dynamically filter the source:
filters = [country: "Sweden"]
from(c in City, where: ^filters)
## Expressions example
City |> where([c], c.country == "Sweden")
City |> where(country: "Sweden")
"""
defmacro where(query, binding \\ [], expr) do
Filter.build(:where, :and, query, binding, expr, __CALLER__)
end
@doc """
An OR where query expression.
Behaves exactly the same as `where` except it combines with any previous
expression by using an `OR`. All expressions have to evaluate to a boolean
value.
`or_where` also accepts a keyword list where each key is a field to be
compared with the given value. Each key-value pair will be combined
using `AND`, exactly as in `where`.
## Keywords example
from(c in City, where: [country: "Sweden"], or_where: [country: "Brazil"])
If interpolating keyword lists, the keyword list entries are combined
using ANDs and joined to any existing expression with an OR:
filters = [country: "USA", name: "New York"]
from(c in City, where: [country: "Sweden"], or_where: ^filters)
is equivalent to:
from c in City, where: (c.country == "Sweden") or
(c.country == "USA" and c.name == "New York")
The behaviour above is by design to keep the changes between `where`
and `or_where` minimal. Plus, if you have a keyword list and you
would like each pair to be combined using `or`, it can be easily done
with `Enum.reduce/3`:
filters = [country: "USA", is_tax_exempt: true]
Enum.reduce(filters, City, fn {key, value}, query ->
from q in query, or_where: field(q, ^key) == ^value
end)
which will be equivalent to:
from c in City, or_where: (c.country == "USA"), or_where: c.is_tax_exempt == true
## Expressions example
City |> where([c], c.country == "Sweden") |> or_where([c], c.country == "Brazil")
"""
defmacro or_where(query, binding \\ [], expr) do
Filter.build(:where, :or, query, binding, expr, __CALLER__)
end
@doc """
An order by query expression.
Orders the fields based on one or more fields. It accepts a single field
or a list of fields. The default direction is ascending (`:asc`) and can be
customized in a keyword list as shown in the examples.
There can be several order by expressions in a query and new expressions
are always appended to the previous ones.
`order_by` also accepts a list of atoms where each atom refers to a field in
source or a keyword list where the direction is given as key and the field
to order as value.
## Keywords examples
from(c in City, order_by: c.name, order_by: c.population)
from(c in City, order_by: [c.name, c.population])
from(c in City, order_by: [asc: c.name, desc: c.population])
from(c in City, order_by: [:name, :population])
from(c in City, order_by: [asc: :name, desc: :population])
A keyword list can also be interpolated:
values = [asc: :name, desc: :population]
from(c in City, order_by: ^values)
## Expressions example
City |> order_by([c], asc: c.name, desc: c.population)
City |> order_by(asc: :name) # Sorts by the cities name
"""
defmacro order_by(query, binding \\ [], expr) do
OrderBy.build(query, binding, expr, __CALLER__)
end
@doc """
A limit query expression.
Limits the number of rows returned from the result. Can be any expression but
has to evaluate to an integer value and it can't include any field.
If `limit` is given twice, it overrides the previous value.
## Keywords example
from(u in User, where: u.id == ^current_user, limit: 1)
## Expressions example
User |> where([u], u.id == ^current_user) |> limit(1)
"""
defmacro limit(query, binding \\ [], expr) do
LimitOffset.build(:limit, query, binding, expr, __CALLER__)
end
@doc """
An offset query expression.
Offsets the number of rows selected from the result. Can be any expression
but it must evaluate to an integer value and it can't include any field.
If `offset` is given twice, it overrides the previous value.
## Keywords example