This time we are back to cover other improvements coming to With Ecto 3.0, it is now possible to add unions\/excepts\/intersects to queries. For example, to get all cities for both customers and suppliers, you can now do:<\/p>\n Keep in mind that Adding support for unions has been a frequently requested feature in Ecto for quite some time. However, all previous approaches to implement this feature were misguided because all of them assumed that we would need to introduce a new data-type that holds the union of two queries.<\/p>\n In other words, in the approaches we had in mind, All of this changed when Timofey Martynov<\/a> sent a pull request<\/a> that adds UNION \/ UNION ALL support by simply treating the UNION \/ UNION ALL as a field in the Let’s see an example. Consider this SQL query:<\/p>\n Which of the queries below is equivalent to the one above?<\/p>\n a) After an informal poll, many chose \n The UNION clause has this general form:<\/p>\n select_statement UNION [ ALL | DISTINCT ] select_statement<\/p>\n select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, or FOR KEY SHARE clause. (ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these clauses will be taken to apply to the result of the UNION, not to its right-hand input expression.)\n<\/p><\/blockquote>\n In other words, UNION\/INTERSECT\/EXCEPTs should be modelled as Ecto 3.0 finally gets support for windows. I mean WINDOWs, not Windows. We have always supported Windows. Ok. This is confusing. Let’s try again.<\/p>\n Ecto 3.0 finally gets support for WINDOW clauses<\/a>, the OVER operator, as well as many WINDOW functions. For example, to compare each employee’s salary with the average salary in their department:<\/p>\n The The first argument should have an aggregator or any of the WINDOW functions<\/a>. By default we support all of the built-in functions found in PostgreSQL and MySQL. They can be found in the This work was contributed by Anton<\/a>. You can read the original discussion in the issues tracker<\/a>.<\/p>\n There are many other exciting changes in We also support FILTER expressions, allowing you filter the value of aggregators: Finally, This finishes the third article on our series about Ecto 3.0. There are many other things we would like to share with you, such as performance improvements, safer migrations and more. We are not quite sure how many articles we still have to write but we are certainly not done. See you soon!<\/p>\n Welcome to the “A sneak peek at Ecto 3.0” series: Breaking changes Query improvements part 1 Query improvements part 2 (you are here!) Performance, migrations and more This time we are back to cover other improvements coming to Ecto.Query in Ecto 3.0. UNION, EXCEPT and INTERSECT With Ecto 3.0, it is now possible to add … \u00bb<\/a><\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[1],"tags":[238,143],"aioseo_notices":[],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/posts\/7839"}],"collection":[{"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/comments?post=7839"}],"version-history":[{"count":15,"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/posts\/7839\/revisions"}],"predecessor-version":[{"id":7880,"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/posts\/7839\/revisions\/7880"}],"wp:attachment":[{"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/media?parent=7839"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/categories?post=7839"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/tags?post=7839"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Ecto.Query<\/code> in Ecto 3.0.<\/p>\nUNION, EXCEPT and INTERSECT<\/h2>\n
customer_city_query = Customer |> select([c], c.city)\nSupplier |> select([s], s.city) |> union(customer_city_query)\n<\/code><\/pre>\nunion<\/code> will attempt to remove any duplicates and that can be expensive. In many cases, especially when you know duplicates cannot happen or you don’t care about returning duplicates, you should use union_all<\/code> instead.<\/p>\nunion(query1, query2)<\/code> would return a new construct similar to Ecto.UnionQuery{left: query1, right: query2}<\/code>. We were skeptical about this as it could push accidental complexity to users of Ecto that would now have to handle different types of queries.<\/p>\nEcto.Query<\/code>, in the same way we store ORDER BY<\/code>s, LIMIT<\/code>s, WHERE<\/code>s and so on. While this direction seemed misguided at first, once we re-read the SQL specification, it became clear that this is the correct way to model UNION \/ UNION ALL.<\/p>\nSELECT city FROM suppliers UNION SELECT city FROM customers LIMIT 10\n<\/code><\/pre>\n(SELECT city FROM suppliers) UNION (SELECT city FROM customers LIMIT 10)<\/code>
\nb) SELECT city FROM suppliers UNION (SELECT city FROM customers) LIMIT 10<\/code><\/p>\na)<\/code> because they expected UNION to work like some top-level, low-precedence operator, but the correct answer is b)<\/code>. The PostgreSQL documentation<\/a> also discusses this:<\/p>\nWHERE<\/code> as they are both considered clauses of a given query and not a top-level operation. This is precisely how it has been implemented in Ecto. The more you know!<\/p>\nWINDOW and OVER support<\/h2>\n
from e in Employee,\nselect: {e.depname, e.empno, e.salary, avg(e.salary) |> over(:department)},\nwindows: [department: [partition_by: e.depname]]\n<\/code><\/pre>\nover\/2<\/code> operator expects either a window name or a window expression as second argument. The query below would return the same results:<\/p>\nfrom e in Employee,\nselect: {e.depname, e.empno, e.salary, avg(e.salary) |> over(partition_by: e.depname)}\n<\/code><\/pre>\nEcto.Query.WindowAPI<\/code> module<\/a> (we are linking to the source as the docs haven’t been released yet).<\/p>\nOther changes<\/h2>\n
Ecto.Query<\/code>. For example, it now has built-in support for coalesce<\/code>, such as select: coalesce(p.title, p.old_title)<\/code>, or even better with the pipe operator: p.field1 |> coalesce(p.field2) |> coalesce(p.field3)<\/code>.<\/p>\nselect: filter(count(), p.public == true)<\/code><\/p>\norder_by<\/code> now supports :asc_nulls_last<\/code>, :asc_nulls_first<\/code>, :desc_nulls_last<\/code>, and :desc_nulls_first<\/code>, allowing you to configure exactly when NULLs are returned when ordering: order_by: [desc_nulls_first: p.title]<\/code>. If you are using :desc<\/code> and :asc<\/code>, then the behaviour is the same as in Ecto 2.0, which is database dependent (and surprise, surprise! they won’t agree with each other).<\/p>\n![\"banner-elixir](\"http:\/\/blog.plataformatec.com.br\/wp-content\/uploads\/2018\/10\/Elixir_2-1-1024x213.png\")
<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"