ecto
ecto copied to clipboard
elixir-ecto
Support values lists
To support values lists in select from, joins, update from and delete from, I added a values(list_of_maps, schema) function that can be used in in expressions like
query = from u in User,
join: v in values([%{id: 1, name: "Moritz"}, ...]), on: u.id == v.id,
update: [set: [name: v.name]]
Repo.update_all(query, [])
This is to support constant tables.
I am opening this PR as a draft early to gather feedback if I should change the syntax before proceeding with the more complicated details.
There's also an ecto_sql PR with support for the postgres adapter already: https://github.com/elixir-ecto/ecto_sql/pull/416
Postgres docs: https://www.postgresql.org/docs/current/queries-values.html MySQL docs: https://dev.mysql.com/doc/refman/8.0/en/values.html MSSQL docs: https://docs.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-ver16
TODO List
- [x] Documentation for
values/1 - [ ] Implement MySQL Adapter
- [ ] Implement MSSQL Adapter
Very clean and elegant, great work. I believe we only need docs and this is good to go. However on the Ecto sql side, you may need you may have trouble handling MySQL. Also please add tests there to make sure that Fields are escaped and that passing two Value lists do not clobber each other.
Oh, additional questions. Do we consider the whole values list as part of the cache key? If so, We don’t need to encode the values as parameters when building the sql and rather put them in the queries.
Alternatively we encode only the field names and the size is cache key, and then we encode parameters.
I guess another option is to encode the whole thing is JSON and then convert JSON to values. This will actually be the best approach if we want to reuse the cache And it likely requires a much smaller PR. It may be worth benchmarking this.
Do we consider the whole values list as part of the cache key?
No, that was just a quick way to get it through the planner.
Alternatively we encode only the field names and the size is cache key, and then we encode parameters.
I think that should work.
I guess another option is to encode the whole thing is JSON and then convert JSON to values. This will actually be the best approach if we want to reuse the cache And it likely requires a much smaller PR. It may be worth benchmarking this.
I've thought about this too, but it feels like extra work on behalf of the user making sure all the types can be dumped to and loaded from json. But I will definitely benchmark this.
I also still need to implement escaping the values into params so the generated query from ecto_sql actually works.
I could use a pointer on how to plumb the values into params with the correct types and all.
I tried doing it in Ecto.Query.Planner.source_cache/2, where I return a list of tuples with the actual value and its type ([{1, :integer}, {"Bob", :string}, ...], but that seems to be at the wrong place since that results in postgrex complaining about having to dump values such as {1, :integer} where it should just be 1.
Nevermind, I think I solved it by explicitely assigning the type to each parameter in the generated SQL.
I have pushed another set of commits that fix the necessary offset in the parameters.
While testing with the inferred type :any I noticed that postgrex (or postgres) always assumes that we want to give strings in all of the untyped (:any) params, so it's not very useful for values lists that contain anything other than strings, unless we actually infer some types directly in ecto by mapping the elixir primitive types to ecto types.
