How to fix `take(p, ^only)` is not a valid query expression

shahryarjb · April 27, 2018, 6:21am

Hello ,

I have 2 problems with Ecto‍‍ for loading all field. please see my following code :

only = [:status, :title, :inserted_at]
		query = from p in PostCategory, order_by: p.inserted_at, select: take(p, ^only)
		Repo.all(query)

my terminal :

== Compilation error in file lib/cms/post/post_query.ex ==
** (Ecto.Query.CompileError) `take(p, ^only)` is not a valid query expression.

* If you intended to call a database function, please check the documentation
  for Ecto.Query to see the supported database expressions

* If you intended to call an Elixir function or introduce a value,
  you need to explicitly interpolate it with ^

    (ecto) expanding macro: Ecto.Query.select/3
    (trangell_cms_service) lib/cms/post/post_query.ex:74: TrangellCmsService.Cms.Post.PostQuery.load_all_category_info/0
    (ecto) expanding macro: Ecto.Query.from/2
    (trangell_cms_service) lib/cms/post/post_query.ex:74: TrangellCmsService.Cms.Post.PostQuery.load_all_category_info/0
** (exit) shutdown: 1
    (mix) lib/mix/tasks/compile.ex:96: Mix.Tasks.Compile.run/1
    (mix) lib/mix/task.ex:314: Mix.Task.run_task/3
    (iex) lib/iex/helpers.ex:94: IEx.Helpers.recompile/0

or I had an error when I was traying to writte folowing code :

	query = from p in PostCategory, order_by: p.inserted_at, select: p.{status, title, inserted_at}
		Repo.all(query)

and its error :

== Compilation error in file lib/cms/post/post_query.ex ==
** (Ecto.Query.CompileError) `p.{status, title, inserted_at}` is not a valid query expression
    (ecto) expanding macro: Ecto.Query.select/3
    (trangell_cms_service) lib/cms/post/post_query.ex:76: TrangellCmsService.Cms.Post.PostQuery.load_all_category_info/0
    (ecto) expanding macro: Ecto.Query.from/2
    (trangell_cms_service) lib/cms/post/post_query.ex:76: TrangellCmsService.Cms.Post.PostQuery.load_all_category_info/0
** (exit) shutdown: 1
    (mix) lib/mix/tasks/compile.ex:96: Mix.Tasks.Compile.run/1
    (mix) lib/mix/task.ex:314: Mix.Task.run_task/3
    (iex) lib/iex/helpers.ex:94: IEx.Helpers.recompile/0

Please help me to fix these error and how to use they.

Thanks.

ref : Dynamically select/restrict fields in a query · Issue #1145 · elixir-ecto/ecto · GitHub

ver my Ecto : 2.1
psql (PostgreSQL) 10.1
Elixir v1.6.4
mac os high sierra

idi527 · April 27, 2018, 7:09am

I don’t think there is a take function, try map instead.

only = [:status, :title, :inserted_at]
query = from p in PostCategory, order_by: p.inserted_at, select: map(p, ^only)
Repo.all(query)

shahryarjb · April 27, 2018, 7:14am

Thank you , it works, I have a question, why doesn’t following code work for me ?

query = from p in PostCategory, order_by: p.inserted_at, select: p.{status, title, inserted_at}
		Repo.all(query)

NobbZ · April 27, 2018, 7:27am

I’m not very deep in ecto, but p.{status, title, inserted_at} looks plain wrong to me. Probably its {p.status, p.title, p.inserted_at} you want?

shahryarjb · April 27, 2018, 7:36am

@NobbZ , please see following link

github.com/elixir-ecto/ecto

Dynamically select/restrict fields in a query

opened 12:56PM - 16 Dec 15 UTC

closed 02:39PM - 03 Feb 16 UTC

josevalim

Note:Discussion Level:Advanced

The issue we are trying to address is that it is impossible to restrict which fi…elds are being returned in a query, both dynamically and statically. For example, imagine you have the following query: ``` elixir from p in Post, where: p.published, select: p ``` However we don't want to fetch all of `p`, only some specific fields. It is not possible to do so today. The aim of this issue is to discuss possible solutions that allows fields to be restricted both statically and dynamically. ## 1. Using {...} syntax One possible solution is to allow the fields to be specified using `{...}`: ``` elixir from p in Post, where: p.published, select: p.{name, title, published_at} ``` Dynamically those fields could be interpolated as a list of atoms: ``` elixir only = [:name, :title, :published_at] from p in Post, where: p.published, select: p.{^only} ``` The limitation of this approach is when we consider the preloads syntax. For example: ``` elixir from p in Post, join: c in assoc(p, :comments), preload: [comments: c] ``` If we want to limit the fields returned by `c`, how to achieve so both statically and dynamically? We have a couple options. One is to allow such directly inside preload, albeit it composes poorly as it requires the fields to be specified alongside the preload: ``` elixir from p in Post, join: c in assoc(p, :comments), preload: [comments: c.{body}] ``` Another option is to specify as a nesting from select, statically: ``` elixir from p in Post, join: c in assoc(p, :comments), preload: [comments: c], select: p.{name, title, published_at, comments.{body}} ``` Such syntax can be used also for filtering the fields from preloads. Dynamically, it looks more elegant: ``` elixir only = [:name, :title, :published_at, comments: :body] from p in Post, join: c in assoc(p, :comments), preload: [comments: c], select: p.{^only} ``` ## 2. Using a function Instead of using `{}`, we could introduce a function called `take`. Statically: ``` elixir from p in Post, where: p.published, select: take(p, [:name, :title, :published_at]) ``` Dynamically: ``` elixir only = [:name, :title, :published_at] from p in Post, where: p.published, select: take(p, ^only) ``` Statically with joins: ``` elixir from p in Post, join: c in assoc(p, :comments), select: take(p, [:name, :title, :published_at, comments: :body]) ``` Dynamically with joins: ``` elixir only = [:name, :title, :published_at, comments: :body] from p in Post, join: c in assoc(p, :comments), select: take(p, ^only) ``` The benefit of `take` is that we can make the static and dynamic syntaxes much closer. ## 3. Using restrict Another option is to introduce a new macro called restrict with the purpose of restricting fields. Statically: ``` elixir from p in Post, where: p.published, restrict: [p: [:name, :title, :published_at]] ``` Dynamically: ``` elixir only = [:name, :title, :published_at] from p in Post, where: p.published, restrict: [p: ^only] ``` Statically with joins: ``` elixir from p in Post, join: c in assoc(p, :comments), restrict: [p: [:name, :title, :published_at, comments: :body]] ``` Dynamically with joins: ``` elixir only = [:name, :title, :published_at, comments: :body] from p in Post, join: c in assoc(p, :comments), restrict: [p: ^only] ``` `restrict` is similar to the function approach, except it is orthogonal, allowing you to choose how fields are filtered apart from how they are structured. Thoughts? /cc @drewolson @jeregrine @chrismccord @ericmj @michalmuskala

from p in Post, where: p.published, select: p.{name, title, published_at}

I wanted to remove additional p flag or I wanted to use dynamically in Ecto. otherwise I knew it .

NobbZ · April 27, 2018, 7:40am

That has been a proposal of three different alternatives, whereas option 2 (a function) was choosen from. The funtion in the proposal was named take/2, but it seems as if it is called map/2 in the actual implementation.