I’m running into another Ecto limitation, it seems it is missing window functions such as over, partition, I’d think it would be something you can add to select but they do not seem to exist as well as select does not seem to take fragments so I’m unsure how I could implement it either. So without dropping to raw untyped/unsafe SQL, how can this be done properly in Ecto itself without calling up a host of (in this case very slow) subqueries?
2 Likes
Sorry…
select definitely takes fragments. Can you show your code?
1 Like
Ah, think I figured it out, I was trying to put a fragment in a specific named slot (significantly shorter), but it seems like it works if the entire select itself is made a fragment (ouch considering I’m dynamically generating the fields requested based on runtime information…), however I’m noing have an issue with returning the data, specifically this:
iex> from(a in DB.Banner.SPRADDR, select: fragment("?, ?, max(?) OVER (PARTITION BY ?)", a.spraddr_pidm, a.spraddr_atyp_code, a.spraddr_seqno, a.spraddr_pidm)) |> Repo.all()
Is only return a list of integers (the pidm’s) instead of all of the results, I’m trying to figure out how to type it, the initial thing that I’m trying would be:
iex> from(a in DB.Banner.SPRADDR, select: type(fragment("?, ?, max(?) OVER (PARTITION BY ?)", a.spraddr_pidm, a.spraddr_atyp_code, a.spraddr_seqno, a.spraddr_pidm), {:integer, :string, :integer})) |> Repo.all()
However, that fails with:
** (Ecto.Query.CompileError) `type(fragment("?, ?, max(?) OVER (PARTITION BY ?)", a.spraddr_pidm(), a.spraddr_atyp_code(), a.spraddr_seqno(), a.spraddr_pidm()), {:integer, :string, :integer})` 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
iex:32: (file)
(ecto) expanding macro: Ecto.Query.from/2
iex:32: (file)
(elixir) expanding macro: Kernel.|>/2
iex:32: (file)
I’ve tried a few other syntax’s to no avail either…
EDIT: Can fragments take a dynamically generated string to generate from, I’m unsure how this would work as the argument count would change as well… The possible return values are 27, so those can be mixed in any order, which is hard to pre-generate… >.>
2 Likes
What’s a named slot? Can you show the code?
What results are missing? Can you show the data you get and the data you expect to get?
Ecto does not support composite types and tuples natively. You need to type each individual value instead of the whole row.
I think the problem is that you are trying to build multiple columns in the fragment. Ecto doesn’t know how to parse that data into individual values since it doesn’t have an SQL parser to parse the code in your fragment. If you split the fragment into individual columns like this it should work better. {a.spraddr_pidm, a.spraddr_atyp_code, fragment("max(?) OVER (PARTITION BY ?)", a.spraddr_seqno, a.spraddr_pidm)}.
1 Like
No, because that would allow for SQL injection. If you do things like that it might be better to drop to normal SQL queries or just use Postgrex directly instead of Ecto.
1 Like
And I tried to replicate what I did before and now it works… >.>
iex> from(a in DB.Banner.SPRADDR, select: %{pidm: a.spraddr_pidm, code: a.spraddr_atyp_code, seq: fragment("max(?) OVER (PARTITION BY ?)", a.spraddr_seqno, a.spraddr_pidm)}) |> Repo.all()
[%{code: "BI", pidm: 30746, seq: 1}, %{code: "BU", pidm: 30746, seq: 1},
%{code: "BU", pidm: 30747, seq: 1}, %{code: "BI", pidm: 30747, seq: 1},
...
I could have sworn I tried a map before… >.>
EDIT: And macro built, I have over/2. ^.^
EDIT: The second argument turned into a list of arguments, it is growing… o.O
2 Likes
And now hitting the issue that I cannot put a fragment in a field list to call like select(query, [g], map(g, ^fields)), hmm…
1 Like
It works here. Please try doing proper reports so we don’t have to guess exactly what is going wrong. Which version are you running? What is the error that you get? What is the whole code snippet?
2 Likes
If I were certain of a bug it would be on the issue tracker, I’m trying to reason through these. ^.^
Ecto 2.1.
I’m trying to do add a couple partitions to a dynamically built list of fields that I want returned in a map, figure I could do this:
iex> fields = [:spraddr_zip, :spraddr_activity_date]
iex> q = from a in DB.Banner.SPRADDR, select: map(a, [{:seqno, fragment("max(?)", a.spraddr_seqno)} | ^fields])
** (CompileError) iex:37: undefined function fragment/2
(ecto) expanding macro: Ecto.Query.select/3
iex:37: (file)
(ecto) expanding macro: Ecto.Query.from/2
iex:37: (file)
I’ve tried a lot of variations, still unable to come up with a way to add a fragment to a run-time generated list of items to return in a map (though here the statement is significantly larger, just testing piecemeal so I can figure out the syntax). I thought a fragment call should be handled by the macro processor but it seems it does not do that in the map call.
EDIT; And I do know my posts have become a lot more terse while I am at work lately. For the past few weeks this Windows 10 piece of crap is running at 1 frame every 2 seconds, so I do not even see what I type except every few seconds. Drivers have been updated, 3d accel of the main windows gui has been disabled and re-enabled a dozen times, I nor IT can figure it out. Thankfully I mostly navigate by keyboard so I just have to wait for an update on occasions but I can type as normal most of the time (though going back to make fixes like the semicolon above is rather amazingly painful, so I’m not ^.^).
Have I mentioned I hate Windows? >.<
1 Like
You can’t use both fragments and the shorthand map syntax. As we can see in the docs of map/2 [1] it only accepts a list of atoms, if you want to use fragments inside maps you need to use the normal map syntax.
1 Like
My point is exactly that it will be much easier for us to help you reason through them if we knew your Ecto version, the full error with stacktrace and the full code snippet.
We need to setup good examples. The amount of information being posted was not useful for having a discussion. 
2 Likes
I do apologize for that, as you’ve seen I’m usually significantly more detailed in the past, just this Windows 10 stupidity makes it exceptionally hard to do that currently, even selecting your quote above was me moving mouse to one end, click and hold, drag to front, wait for screen to update to see if I’ve placed it right, move around to fix, wait for update, until I finally release, then wait about 10 seconds for the ‘quote’ to pop up. I’m typing entire sentences here at a time before I see it appear. So again, have I mentioned that I hate Windows? ^.^;
1 Like
Do you have an AV installed ? If yes, it is usually always the reason for slugginess …
1 Like
Had to disable it because it being enabled made elixir compiles take ten+ minutes where without it an elixir compile only takes about a minute and a half. ^.^
But yeah, it is disabled right now, unsure how safe that is but eh, maybe if something happens they’ll let me install linux instead. ^.^
Our new linux server is almost setup, I cannot wait until I can finally deploy this elixir app to it instead of behind IIS.
But yeah, the sluggishness is different than anything I’ve seen before. Like I move the mouse to the start button, it takes a few seconds to highlight, I click, after a second it kind of half draws, then another 2 seconds it finishes drawing all at once, just everything is ‘frozen’ most of the time, every second to three it ‘updates’, sometimes everything is perfectly fluid for about half a second, fully responsive, then goes back to freezing again. There is nothing using CPU, ram is only a tiny 8 gigs and it is pretty well used but not entirely (still over a gig free of ram it says, though this is much harder to read than on linux… so I’m unsure). We’ve looked at a lot of things (the IT people usually ask me for help so they had no ideas that I did not already try) and not found the reason. It is a decently high end business class Dell Desktop, multi-monitor, etc… If you have any more ideas that I’ve not found at Google I would love to hear. ^.^
EDIT: Oh, and interestingly tooltips appear faster on many things, like the taskbar at the bottom, even before the screen ‘updates’, so I know the changes are happening immediately, I.E. before the draw update, so it seems to be entirely visual, hence our thoughts of video card and such, but a variety of drivers had no changes, system is fully up to date otherwise (as Windows 10 forces on you via often rebooting while in the middle of working to apply updates… thankfully they are stopping that they say)
EDIT2: Whoo, this huge Ecto thing I built is now successfully generating multi-page long SQL commands to get the information from this horror of a database in a decently reasonable amount of time, only 1.5s, which is the same as my hand-crafted SQL commands that do less. ^.^
1 Like
