Ecto subqueries with virtual fields?

I’m playing with schema virtual fields in order to select computed values without having to pollute the database with empty fields.

Seems that I can use virtual fields for that, except they won’t work within a subquery.

So:
from u in User, select: %{u | computed: fragment("1")}
works ok,
while

query = from u in User, select: %{u | computed: fragment(“1”)}
q = from q in subquery(query), where: q.computed == “1”

Doesn’t.

The interesting this is that it fails not at compilation time, but when doing Repo.all(q).

Is this an issue, or working as expected or simply I’m missing something?

I’ve setup a demo repo here:
https://github.com/xadhoom/ecto_virtuals_test

with two tests which demonstrate my issue
https://github.com/xadhoom/ecto_virtuals_test/blob/master/test/ecto_virtuals_test_test.exs

1 Like

The error is

 ** (Ecto.SubQueryError) the following exception happened when compiling a subquery.

     ** (Ecto.QueryError) invalid key `:computed` on map update in subquery in query:

     from u in EctoVirtualsTest.User,
       select: %{u | computed: fragment("1")}


 The subquery originated from the following query:

 from u in subquery(from u in EctoVirtualsTest.User,
   select: %{u | computed: fragment("1")}),
   where: u.computed == "1",
   select: u

and the used schema is here:
https://github.com/xadhoom/ecto_virtuals_test/blob/master/lib/ecto_virtuals_test/user.ex

How do you persist your schema in the database? I can’t find it in the sample project.

P.S.: I might be wrong and I hope someone more knowledgeable will clarify this but I think the :virtual option on the field is not meant to work like this. It is just a field in the struct that you assign to it but it is not persisted in the database. This means you can’t use it as a filter. Again… maybe I am wrong and really would like some on else’s opinion on this.

Maybe your situation is similar to this post

You can do a lot of things with the calculated index without using fragments. But do read the whole thread. There were some objections against that method.

There’s no persistence, I’ve setup the sample just to run a very simple query and show up the error.

Well, my point is that the error says that the map update points to an invalid key when in a subquery, but does not says anything when in a normal select.

Same happens if the subquery is without a filter:

query = from u in User, select: %{u | computed: fragment(“1”)}
q = from q in subquery(query), select: q
Repo.all(q)

In my repo I used ecto master, but same happens with 2.2

I think its because the virtual fields aren’t getting selected in the subquery since they don’t actually exist in the repository. For example, look at the generated sql of the subquery:

iex(38)> q = from u in User, select: %{u | computed: fragment("1")}
iex(39)> EctoVirtualsTest.Repo.to_sql :all, q
{"SELECT u0.\"id\", u0.\"username\", u0.\"password\", u0.\"inserted_at\", u0.\"updated_at\", 1 FROM \"users\" AS u0",

There is no computed field in the select statement because virtual fields exist inside the schema/changesets only, not inside of the Repo. The main query is looking for a computed field from this subquery which doesn’t exist.

I’m not sure what the best way to do what you want is, someone more knowledgeable about Ecto can probably help you out there. I know you can use anything that implements Ecto.Queryable as a subquery, so maybe there is a way to preform the subquery and generate the changesets so that the virtual fields are all derived, and then somehow use that collection of changesets as an Ecto.Queryable in the subquery. There might be simpler way though.

Though I think generally the Repo aspect of Ecto is all about querying the datastore, and the purpose of virtual fields is that they don’t exist in the datastore, so you may need to take a different approach.

2 Likes

Well, yes I may agree here.

Just trying to understand why on a plain query selecting a map with a virtual fields works and I cannot do the same within a subquery.

Just the experience is not consistent.

In my use case I’m computing a field using sql functions and the only way to have it in the returned struct is to use schema virtual fields. (and I can also cast it to a custom type!)

Which works wonderfully for normal queries, but not in subqueries.

As now, I’m just adding a useless field in my schema/migrations just to make it work.

But yes, I would like to have some clarification from someone more expert on the matter :slight_smile:

Please open up an issue. We should either make it work or raise an error. But generally speaking, subqueries support only a subset of what actual queries do.

It is also worth noting that you can do this in subqueries: %{foo: p.foo, bar: p.bar, title: some_expr} which would solve your problem albeit in a more verbose way?

1 Like

well yes, but is a bit less handy when your query is dynamic and you don’t know the model.
Maybe with a little more boilerplate to create the map (mmmh but then it will need to be interpolated, did not tried it yet)

Ok, I’ll open an issue, thanks for the feedback!

Opened https://github.com/elixir-ecto/ecto/issues/2260 to keep track.

I think I got it to work with select_merge instead of select:

inner = from u in User, select_merge: %{computed: fragment("FOO")}
outer = from q in subquery(inner), where: q.computed == "FOO"

iex(68)> Repo.to_sql :all, outer
{"SELECT s0.\"id\", s0.\"username\", s0.\"password\", s0.\"inserted_at\", s0.\"updated_at\", s0.\"computed\" FROM (SELECT u0.\"id\" AS \"id\", u0.\"username\" AS \"username\", u0.\"password\" AS \"password\", u0.\"inserted_at\" AS \"inserted_at\", u0.\"updated_at\" AS \"updated_at\", FOO AS \"computed\" FROM \"users\" AS u0) AS s0 WHERE (s0.\"computed\" = 'FOO')",
 []}

This doesn’t trigger the error and the generated sql query looks correct.

mmmh I cannot make it work.
Have you tried running Repo.all(outer) ?

I’ve applied this to my test repo:

diff --git a/test/ecto_virtuals_test_test.exs b/test/ecto_virtuals_test_test.exs
index 3ad1972..cd16d16 100644
--- a/test/ecto_virtuals_test_test.exs
+++ b/test/ecto_virtuals_test_test.exs
@@ -13,7 +13,7 @@ defmodule EctoVirtualsTestTest do
   end
 
   test "subquery with computed virtual field" do
-    query = from u in User, select: %{u | computed: fragment("1")}
+    query = from u in User, select_merge: %{u | computed: fragment("1")}
 
     q = from q in subquery(query), where: q.computed == "1"

and get same error

In the select_merge you don’t need the map update syntax, just a regular single-key map.

So it is:

%{computed: fragment("1")}

rather than:

%{u | computed: fragment("1")}

Since it is selecting from User you are already getting all of the user fields, and then you just need to merge in the single derived field. That is my take on it at least.

I am seeing an error with Repo.all though and the generated SQL. It is trying to selected column FOO rather than the string literal "FOO" so it is throwing an error. There may be a way around that though.

1 Like

Great!

yes it works, even tried on my app :slight_smile:

many thanks, I’ll update the issue on ecto and will see what mantainers say on the question, maybe they’ll shed out some light or clarify the error, to point user to select_merge :slight_smile:

I can get your example to work using fragment("1") since that becomes 1 AS "computed" which is valid as an integer literal. But since I used a string literal in my example instead it needs to be fragment("'FOO'::text") to give postgres more context. Just a heads up if you need to use strings.

yes, in fact in my app I’m casting the fragment using a custom type.