Let’s get started with the improvements to Ecto.Query APIs. The Let’s get started.<\/p>\n Ecto has always supported joining over multiple schemas and tables using joins:<\/p>\n Now imagine we want to modify the query above to only return comments that are public. We could compose on the query above as follows:<\/p>\n As you can see in the example above, we can extract all existing bindings in a query ( The problem with positional bindings is that sometimes it makes query composition quite challenging. When building complex search functionality, you may join over multiple tables, in a different order, and tracking where each positional binding is would be quite brittle and complex.<\/p>\n Ecto 3.0 changes this by allowing each Note we have added the We replace the positional binding by a keyword list, where the key is the binding name and the value is a variable we will assign the join to. Once again, the Note Ecto 3.0 chose to introduce an explicit naming mechanism via the Finally, keep in mind that the Named bindings will make Ecto much more flexible for building dynamic queries, as usually seen in complex search forms, search APIs and more. The bulk of the work was done by Adrian Gruntkowski<\/a>. You can read on the proposal and the following discussion<\/a> in the issues tracker.<\/p>\n We have two new functionalities on top of the foundation we created to add named bindings to Ecto: per from\/join prefixes and index hints.<\/p>\n Ecto v2.0 introduced the idea of prefixes. What the prefix means depends on the database engine. For Postgres, the prefix translates to a Postgres Schema. A database in Postgres has multiple schemas and the default schema is called “public”. MySQL does not support schemas, therefore the prefix functionality in MySQL simply translates to different databases.<\/p>\n When Ecto v2.0 introduced prefixes, the goal was to make it straightforward to select, insert, update and delete data from different prefixes. The goal was to support multi-tenant applications. However, Ecto v2.0 was limited to only work on a single prefix at a time. For example, it was not possible to write a query that would join data across two different prefixes.<\/p>\n Ecto v3.0 lifts this restriction by allowing the Similarly, Ecto 3.0 relies on a similar API to support the use of index hints, as found in MySQL<\/a> and MSSQL<\/a> databases:<\/p>\n Keep in mind you want to use hints rarely, so don’t forget to read the database disclaimers about such functionality.<\/p>\n The prefix and hints options brings more flexibility to developers to structure and optimize their queries, allowing them to leverage Ecto.Query as much as possible, without having to fallback to SQL.<\/p>\n Ecto.Query now supports tuples in We have also added support for arithmetic operators, such as Finally, it is now possible to invoke database functions that expect the whole table\/source as argument, by using fragments: This is it for now! If you have any questions about the features above, feel free to use the comments section below or search for the relevant discussion in Ecto’s issues tracker. Next week we will be back with further improvements and features added to Ecto.Query in Ecto 3.0.<\/p>\n Welcome to the “A sneak peek at Ecto 3.0” series: Breaking changes Query improvements part 1 (you are here!) Query improvements part 2 Performance, migrations and more Let’s get started with the improvements to Ecto.Query APIs. The Ecto.Query API is the area that saw most improvements in Ecto 3.0, to the point we won’t be … \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\/7825"}],"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=7825"}],"version-history":[{"count":11,"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/posts\/7825\/revisions"}],"predecessor-version":[{"id":7881,"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/posts\/7825\/revisions\/7881"}],"wp:attachment":[{"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/media?parent=7825"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/categories?post=7825"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/tags?post=7825"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Ecto.Query<\/code> API is the area that saw most improvements in Ecto 3.0, to the point we won’t be able to cover all improvement in a single article. Instead, we broke it in part 1 and part 2.<\/p>\nBetter join composition with named bindings<\/h2>\n
query =\n from p in Post,\n join: c in Comment,\n where: p.id == c.post_id,\n select: c\n<\/code><\/pre>\nfrom [_, c] in query, where: c.public\n<\/code><\/pre>\np<\/code> and c<\/code>) and then apply filters to them. In the example above, the bindings are positional and they depend on the order they appear in the list on the left side of in<\/code>. The names p<\/code> and c<\/code> are temporary and they are not relevant to the overall query. In other words, the query below would be equivalent to the one above:<\/p>\nfrom [_, comment] in query, where: comment.public\n<\/code><\/pre>\nfrom<\/code> and join<\/code> to have a name. Our initial query could be rewritten as:<\/p>\nquery =\n from p in Post,\n join: c in Comment,\n as: :comments,\n where: p.id == c.post_id,\n select: c\n<\/code><\/pre>\nas<\/code> option after the join. Now to filter the existing :comments<\/code>, regardless of the order it appears on the query, we can write:<\/p>\nfrom [comments: c] in query, where: c.public\n<\/code><\/pre>\nc<\/code> variable here does not matter and it could have any name. The important bit is that we are binding it to the existing :comments<\/code>.<\/p>\n:as<\/code> option, instead of relying on the variable names, as the variable names could lead to accidental clashing, especially as developers may shortcut the variable names to single letters in queries. Furthermore, if there is an attempt to bind to the same name more than once, an error will be raised.<\/p>\nas<\/code> option can also be given to from<\/code>, for instance:<\/p>\nquery =\n from p in Post,\n as: :posts,\n join: c in Comment,\n as: :comments,\n where: p.id == c.post_id,\n select: c\n<\/code><\/pre>\nDatabase prefixes and index hints<\/h2>\n
prefix<\/code> option to be given to from<\/code>\/join<\/code>, in the same way we could pass the as<\/code> option. For example, imagine that you have a system where all of the posts are public but the comments are specific to each client using the system. Therefore, you have multiple prefixes in the system, one for each client, and each prefix has its own “comments” table. You can now query across those prefixes as follows:<\/p>\nfrom p in Post,\n prefix: \"public\",\n join: c in Comment,\n prefix: \"client1\",\n where: p.id == c.post_id,\n select: c\n<\/code><\/pre>\nfrom p in Post,\n join: c in Comment,\n hints: [\"USE INDEX FOO\", \"USE INDEX BAR\"],\n where: p.id == c.post_id,\n select: c\n<\/code><\/pre>\nOther changes<\/h2>\n
where<\/code> and having<\/code>, allowing queries such as where: {p.foo, p.bar} > {^foo, ^bar}<\/code> which can be used for cursor-based pagination<\/a>.<\/p>\n+<\/code>, -<\/code>, *<\/code>, \/<\/code>. Note those operators just delegate to the underlying database engine, so remember to check your database to see what are the possible types of the operands.<\/p>\nfragment(\"some_function(?)\", p)<\/code>.<\/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":"