Ecto query working with computed values

Questions / Help
1

I have a really crazy query that handles one computed value with struct values, but i have seem to keep finding blockers. I want to ideally keep the struct, but have also include the computed value rownum. Basically i’m working with an older version of Oracle that seems to have a really weird way of doing pagination using various subqueries:

select o.* from (select o.*, rownum as r__ from (select * from o_order ORDER BY id) o where rownum <= 60) o where r__ >= 21;

schema "o_order" do
        field :externalid, :string
        
        belongs_to :store, MesaHub.Model.Store, foreign_key: :storeid
        has_many :items, MesaHub.Model.OrderItem, foreign_key: :orderid

        field :status, :string
        field :statusreason, :string
        field :customerid, :integer

        field :createdby, :string
        field :modifiedby, :string
    end

The closest i’ve come to succeeding:

w1 = from o in MesaHub.Model.Order
w2 = from o in subquery(w1), where: fragment("rownum <= 60"), select: %{foo: o.id, test: o.externalid}, select_merge: %{r: fragment("rownum")}
w3 = from o in subquery(w2), where: fragment("? >= 21", o.r)

SELECT s0.foo, s0.test, s0.r FROM ((SELECT s0.id foo, s0.externalid test, rownum r FROM ((SELECT o0.id id, o0.externalid externalid, o0.storeid storeid, o0.status status, o0.statusreason statusreason, o0.customerid customerid, o0.createdby createdby, o0.modifiedby modifiedby FROM o_order o0)) s0 WHERE (rownum <= 60))) s0 WHERE (s0.r >= 21) []

Here i’ve managed to get pretty close to the actual query itself, however i find this pretty inflexible for creating a generic way to paginate objects, firstly i would need to always map all the keys of the struct and secondly i don’t have a nice list of structs at the end.

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

    ** (Ecto.QueryError) maps, lists, tuples and sources are not allowed as map values in subquery, got: `%{o: &0}` in query:

    from o0 in subquery(from o0 in MesaHub.Model.Order,
      select: %MesaHub.Model.Order{id: o0.id, externalid: o0.externalid, storeid: o0.storeid, status: o0.status, statusreason: o0.statusreason, customerid: o0.customerid, createdby: o0.createdby, modifiedby: o0.modifiedby}),
      where: fragment("rownum <= 60"),
      select: merge(%{o: o0}, %{r: fragment("rownum")})


The subquery originated from the following query:

from o0 in subquery(from o0 in subquery(from o0 in MesaHub.Model.Order),
  where: fragment("rownum <= 60"),
  select: merge(%{o: o0}, %{r: fragment("rownum")})),
  where: fragment("? >= 21", o0.r),
  select: o0

    (ecto) lib/ecto/repo/queryable.ex:132: Ecto.Repo.Queryable.execute/4
    (ecto) lib/ecto/repo/queryable.ex:18: Ecto.Repo.Queryable.all/3

I also tried something like this… but naturally it doesn’t accept it.

3

I haven’t dealt with Oracle in like 10 years so my question is probably naive but… isn’t there a simpler way to use rownum instead of two nested sub-queries?

4

Apparently not :frowning: , in the latest version it supports LIMIT and OFFSET but 11 uses rownum.

5

Ahh old Oracle versions, that’s my world too. ^.^;

Either add a virtual field (virtual: true) to the structure, or just convert it all to a map in the query instead of a structure in the select: ... part. I opt for a virtual field often. I quite often make new customized ecto structs just for specific queries too so don’t hesitate to do that either! :slight_smile:

1 Like